(mysql.info.gz) GROUP BY optimization

(mysql.info.gz) ORDER BY optimization

(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.

(mysql.info.gz) GROUP BY optimization

Menu