Optimize GROUP BY a ORDER BY b in mysql
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.