Re: Optimize sub query with large group by

[Øystein Grøvlen]

I think the following query should give you the result you are looking for:

SELECT person_id, COUNT(id) FROM events
WHERE (object_id, time) IN
(SELECT object_id, MAX(time)
FROM events
GROUP BY object_id)
GROUP BY person_id;

I do not know whether this will be faster, but according the EXPLAIN output, it does not involve the file sorts that your query seems to have.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway