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 through 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.
GROUP BY is used for sorting, so the server may also apply
ORDER BY optimizations to grouping. See Section
ORDER BY Optimization".