MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize sub query with large group by
Posted by: Øystein Grøvlen
Date: March 16, 2011 02:02AM

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

Options: ReplyQuote

Written By
March 10, 2011 07:44PM
Re: Optimize sub query with large group by
March 16, 2011 02:02AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.