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