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

Answer 1

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;