A proposed solution for aggregate function for "LAST" in MySQL

Question

I have a log table which looks as follows:

logs (_id_, client, metric, value);

I am attempting to write the following query in MySQL from a PHP front end to get information out of a log table.

SELECT client, LAST(value) AS value
FROM logs 
WHERE metric = 'free space'
GROUP BY client;

Except, of course, that LAST is not a valid aggregate function.

My proposed solution is this:

SELECT client, value 
FROM logs 
WHERE id IN (
   SELECT MAX(id) 
   FROM logs 
   WHERE metric = 'free space' 
   GROUP BY client);

However, for a very small table of 4,000 rows, this query takes in excess of 60 seconds to execute on my development machine.

Suggestions?

Answer 1

You should do this with a join

SELECT l.client, l.value 
  FROM logs l
  JOIN ( SELECT client, MAX(id) as id
           FROM logs 
          WHERE metric = 'free space' 
          GROUP BY client ) s
    ON l.id = s.id

You should also have a (unique if possible) index on id for the outer query, this enables the join to use the indexes. If optimisation is massively important you may also want an index on client, id, or possibly id, client, for the sub-select, though this isn't as important as you have to scan the entire table anyway.

Please also see SQL JOIN vs IN performance? and IN vs. JOIN with large rowsets for discussions around using in or join.

Answer 2

This query provides the latest row after performing the group by operation. Joining of another table is also shown.

SELECT messages.* , users.id AS user_id, users.first_name, users.last_name FROM messages LEFT JOIN users ON users.id = messages.sender_id WHERE messages.id IN ( SELECT MAX(messages.id) FROM messages WHERE messages.receiver_id = 2 GROUP BY messages.sender_id);