Optimize GROUP BY a ORDER BY b in mysql
by ewout
The following query took 150 ms on a dataset of a few thousand rows, even though there were indexes on companies.id, companies.name and people.company_id.
SELECT companies.id, count(people.id) FROM companies LEFT JOIN people on companies.id = people.company_id GROUP BY companies.id ORDER BY companies.name
Explain revealed “Using index; Using temporary”. Turns out that mysql can only use a single index for grouping and sorting. When sorting and grouping on different columns, a temporary table needs to be created and sorted after grouping.
The solution? GROUP BY companies.name, companies.id. The query now takes under 10 ms.
![Fork me on GitHub](http://s3.amazonaws.com/github/ribbons/forkme_left_red_aa0000.png)
Comments
were you able to get rid of “Using temporary” using group by the 2 columns ?
Yes, now it says “using index” only. The index on companies.name should only be on that column though. If you have an index on companies.name, companies.short_name for instance, you need to GROUP BY companies.name, companies.short_name, companies.id.