Re: Optimizing a query with group by and order by count

[Rick James]

"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.