tuning a SQL query for better performance

I have this SQL statement that work开发者_运维知识库s but takes a while to execute

I have an activity table and I need to find the last activity and the associated user for each id.

SELECT id, date_time, user  
FROM activity_log a 
WHERE a.date_time = (SELECT MAX(a1.date_time)
                FROM activity_log a1
                WHERE a.id = a1.id
                GROUP BY id)
ORDER BY `id` desc limit 0, 100

I have a non unique index on date_time field and id field.

How can we get a shorter execution time on this query?

What you currently have is a correlated subquery, which requires a computation on each of the rows you return from your outer select.

Instead, return the entire dataset of id and max(date_time) as a subquery and join to that. That requires only 1 trip to the activity_log table to find each max(date_time) and will significantly improve your runtimes.

SELECT a.id, a.date_time, a.user  
FROM activity_log a 
INNER JOIN (
  SELECT id, MAX(date_time) as date_time
                FROM activity_log 
                GROUP BY id) a1
ON a.id = a1.id and a.date_time = a1.date_time
ORDER BY `id` desc limit 0, 100

What happends if you try this:

SELECT id, date_time, user   
FROM activity_log a  
WHERE EXISTS 
(SELECT 1 FROM (SELECT ID,MAX(a1.date_time) maxdate
                FROM activity_log a1 
                GROUP BY ID) a1 WHERE A1.ID=A.ID AND A1.MAXDATE=a.date_time)
ORDER BY `id` desc limit 0, 100