(mysql.info) group-by-optimization
Info Catalog
(mysql.info) order-by-optimization
(mysql.info) query-speed
(mysql.info) limit-optimization
7.2.13 `GROUP BY' Optimization
------------------------------
Menu
* loose-index-scan Loose index scan
* tight-index-scan Tight index scan
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.
Info Catalog
(mysql.info) order-by-optimization
(mysql.info) query-speed
(mysql.info) limit-optimization
automatically generated byinfo2html