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