(mysql.info) loose-index-scan

(mysql.info) group-by-optimization
 
 7.2.13.1 Loose index scan
 .........................
 
 The most efficient way to process `GROUP BY' is when the index is used
 to directly retrieve the group fields. With this access method, MySQL
 uses the property of some index types that the keys are ordered (for
 example, `BTREE'). This property enables use of lookup groups in an
 index without having to consider all keys in the index that satisfy all
 `WHERE' conditions.  This access method considers only a fraction of
 the keys in an index, so it is called a loose index scan. When there is
 no `WHERE' clause, a loose index scan reads as many keys as the number
 of groups, which may be a much smaller number than that of all keys. If
 the `WHERE' clause contains range predicates (see the discussion of the
 `range' join type in  explain), a loose index scan looks up the
 first key of each group that satisfies the range conditions, and again
 reads the least possible number of keys.  This is possible under the
 following conditions:
 
    * The query is over a single table.
 
    * The `GROUP BY' includes the first consecutive parts of the index.
      (If, instead of `GROUP BY', the query has a `DISTINCT' clause, all
      distinct attributes refer to the beginning of the index.)
 
    * The only aggregate functions used (if any) are `MIN()' and
      `MAX()', and all of them refer to the same column.
 
    * Any other parts of the index than those from the `GROUP BY'
      referenced in the query must be constants (that is, they must be
      referenced in equalities with constants), except for the argument
      of `MIN()' or `MAX()' functions.
 
 The `EXPLAIN' output for such queries shows `Using index for group-by'
 in the `Extra' column.
 
 The following queries fall into this category, assuming that there is
 an index `idx(c1,c2,c3)' on table `t1(c1,c2,c3,c4)':
 
      SELECT c1, c2 FROM t1 GROUP BY c1, c2;
      SELECT DISTINCT c1, c2 FROM t1;
      SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
      SELECT c1, c2 FROM t1 WHERE c1 < CONST GROUP BY c1, c2;
      SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > CONST GROUP BY c1, c2;
      SELECT c2 FROM t1 WHERE c1 < CONST GROUP BY c1, c2;
      SELECT c1, c2 FROM t1 WHERE c3 = CONST GROUP BY c1, c2;
 
 The following queries cannot be executed with this quick select method,
 for the reasons given:
 
    * There are aggregate functions other than `MIN()' or `MAX()', for
      example:
 
           SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
 
    * The fields in the `GROUP BY' clause do not refer to the beginning
      of the index, as shown here:
 
           SELECT c1,c2 FROM t1 GROUP BY c2, c3;
 
    * The query refers to a part of a key that comes after the `GROUP
      BY' part, and for which there is no equality with a constant, an
      example being:
 
           SELECT c1,c3 FROM t1 GROUP BY c1, c2;
Info Catalog
(mysql.info) group-by-optimization
(mysql.info) tight-index-scan
automatically generated byinfo2html