(mysql.info.gz) GROUP BY optimization
Info Catalog
(mysql.info.gz) ORDER BY optimization
(mysql.info.gz) Query Speed
(mysql.info.gz) LIMIT optimization
7.2.11 How MySQL Optimizes `GROUP BY'
-------------------------------------
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 the index stores its keys in order (for example, this is a B-Tree
index, and not a HASH index). Whether usage 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.
Menu
* Loose index scan Loose index scan
* Tight index scan Tight index scan
Info Catalog
(mysql.info.gz) ORDER BY optimization
(mysql.info.gz) Query Speed
(mysql.info.gz) LIMIT optimization
automatically generated byinfo2html