MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizing a query with group by and order by count
Posted by: Rick James
Date: March 21, 2011 10:08AM

"has millions of records ... it takes about 2.5 secs to execute" -- Yes, it has to do a lot of work.

Without the ORDER BY, it can return any 200 ("LIMIT 0, 200") rows it feels like. So, it does not need to do nearly as much work.

In particular, it sounds like you have an INDEX starting with SIC; correct? That means that the GROUP BY can be handled by reading the index in order. Then the query without the ORDER BY can stop after only 200 distinct values of SIC (maybe more because of the HAVING).

The version with the ORDER BY has to read the _entire_ list, filter (HAVING), sort (ORDER BY), and only then do the LIMIT.

Two approaches to optimization...

* Re-think the application so that you don't need the query.

* "Summary table". It would keep a tally of how many of each SIC for each (say) day. Then you would do the query against the summary table. Since the summary table may be 10x smaller, it might run 10x faster. I can't be more specific without getting a feel for your table, your data, what SIC is, etc.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimizing a query with group by and order by count
2810
March 21, 2011 10:08AM


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.