(mysql.info) group-by-optimization

情報カタログ

(mysql.info) order-by-optimization

(mysql.info) query-speed

(mysql.info) limit-optimization

 
 7.2.13 `GROUP BY' Optimization
 ------------------------------
 

メニュー

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

情報カタログ

(mysql.info) order-by-optimization

(mysql.info) query-speed

(mysql.info) limit-optimization