Re: Query with GROUP BY not using Index
Hi,
A temporary table is involved in the original query, too, since the result of the sub-query will be stored in a temporary table. However, in that case MySQL is able to detect that it can use an index efficiently for the GROUP BY, and the temporary table is smaller since the aggregation has already been performed. For the simplified query, the aggregation is done on the temporary table. Hence, this temporary will be much bigger.
I notice that I made a mistake in the query that I proposed. It should of course be "ORDER BY MIN(t1.date)" (or just "ORDER BY 2"). I do not know whether that would make a difference.
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway