7.2.13. GROUP BY Optimization
The most general way to satisfy a GROUP BY
clause is to scan the whole table and create a new temporary
table where all rows from each group are consecutive, and then
use this temporary table to discover groups and apply aggregate
functions (if any). In some cases, MySQL is able to do much
better than that and to avoid creation of temporary tables by
using index access.
The most important preconditions for using indexes for
GROUP BY are that all GROUP
BY columns reference attributes from the same index,
and that the index stores its keys in order (for example, this
is a BTREE index and not a
HASH index). Whether use of temporary tables
can be replaced by index access also depends on which parts of
an index are used in a query, the conditions specified for these
parts, and the selected aggregate functions.
There are two ways to execute a GROUP BY
query via index access, as detailed in the following sections.
In the first method, the grouping operation is applied together
with all range predicates (if any). The second method first
performs a range scan, and then groups the resulting tuples.
