Keith D Kaiser Keith D Kaiser - 5 days ago 5
MySQL Question

How to make MySQL run more efficiently?

This query returns a suggestion list (hint) from a reliably small table, several thousand rows currently. But it returns the hint far too slowly to be very useful. Most of the other questions suggest polling the DB for where the slowdown is. I really don't have access at the level necessary to do that. It currently looks like below, maybe someone can help me see a technique I don't know. I would really like to speed up the following query.

$stmt = $db_found->prepare("SELECT DISTINCT callsign, Fname, ID, grid,
tactical, latitude, longitude, email, Lname
FROM NetLog
WHERE recordID IN (SELECT max(recordID)
FROM NetLog
WHERE callsign LIKE ?
AND callsign NOT IN ('W0KCN','WA0QFJ','T0EST','K0ERC','NR0AD')
GROUP BY callsign)");


So that it pulls up the most current version of the recordID. recordID is the auto-increment variable in this table. If I remove the first FROM and WHERE then the query executes much faster. Like here;

$stmt = $db_found->prepare("SELECT DISTINCT callsign, Fname, ID, grid, tactical, latitude,
longitude, email, Lname, recordID
FROM NetLog
WHERE callsign LIKE ?
AND callsign NOT IN ('W0KCN','WA0QFJ','T0EST','K0ERC','NR0AD')
GROUP BY callsign");


The problem is there is no guarantee that this is the most current record.

How can I speed up the return, and still be sure I'm getting the most current recordID for this callsign?

Answer

in addition to verifying the actual operation with an execution plan you can try to approach the data differently

You could try using a dinamic (temp) table instead of a IN clause

$stmt = $db_found->prepare("
SELECT DISTINCT 
    callsign
  , Fname
  , ID
  , grid
  , tactical
  , latitude
  , longitude
  , email
  , Lname
FROM NetLog 
INNER JOIN ( 
    SELECT max(recordID) as recordID
    FROM NetLog 
    WHERE callsign LIKE  ?
    AND callsign NOT IN ('W0KCN','WA0QFJ','T0EST','K0ERC','NR0AD')
    GROUP BY callsign
  ) t1 on t1.recordID = NetLog.recordID;");
Comments