How to improve SQL query performance in MySQL

Question

I have a MySQL table which stores backup log entries from a large number of devices (currently about 750). I need to use a query to get details of the last entry for each device. I am currently using a nested query to achieve this, which was working fine initially. However the table now has thousands of rows and the query is taking a long time to run. I would like to improve the performance of the query, and would like to know if this is possible through the use of joins instead of a nested select statement, or through some other improvement I could make.

The current query is:

SELECT id, pcname, pcuser, checkTime as lastCheckTime,
  TIMESTAMPDIFF(SECOND,checkTime,now()) as lastCheckAge,
  lastBackup, diff, threshold, backupStatus,
  TIMESTAMPDIFF(SECOND,lastBackup,now()) as ageNow

FROM checkresult

WHERE (checkTime, pcname) IN
(
    SELECT max(checkTime), pcname
    FROM checkresult
    GROUP BY pcname
)

ORDER BY id desc;

id is the primary key for the table, and is the only indexed column.

The table uses InnoDB

Jet86 · Accepted Answer · 2014-09-16 03:49:06Z

4

Try using an explicit join instead:

SELECT id, checkresult.pcname, pcuser, checkTime as lastCheckTime,
       TIMESTAMPDIFF(SECOND,checkTime,now()) as lastCheckAge,
       lastBackup, diff, threshold, backupStatus,
       TIMESTAMPDIFF(SECOND,lastBackup,now()) as ageNow
FROM checkresult join
     (SELECT pcname, max(checkTime) as maxct
      FROM checkresult
      GROUP BY pcname
     )  pm
     on checkresult.pcname = pm.pcname and checkresult.checkTime = pm.maxct
ORDER BY id desc;

edited Sep 16, 2014 at 3:49

Jet86

1054 bronze badges

answered Sep 16, 2014 at 3:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jet86 Over a year ago

Thanks @GordonLinoff - this worked perfectly and reduced the execution time ten-fold (perhaps more).

Collectives™ on Stack Overflow

How to improve SQL query performance in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related