(mysql.info.gz) Loose index scan

Info Catalog

(mysql.info.gz) GROUP BY optimization

(mysql.info.gz) GROUP BY optimization

(mysql.info.gz) Tight index scan

 
 7.2.11.1 Loose index scan
 .........................
 
 The most efficient way is when the index is used to directly retrieve
 the group fields. With this access method, MySQL uses the property of
 some index types (for example, B-Trees) that the keys are ordered. This
 property allows use of lookup groups in an index without having to
 consider all keys in the index that satisfy all `WHERE' conditions.
 Since this access method considers only a fraction of the keys in an
 index, it is called "loose index scan." When there is no `WHERE' clause,
 a loose index scan will read as many keys as the number of groups, which
 may be a much smaller number than all keys. If the `WHERE' clause
 contains range predicates (described in  `EXPLAIN' EXPLAIN, under
 the `range' join type), 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, then
      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 index parts than the ones from `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 provide several examples that fall into this
 category, assuming 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 other aggregate function than `MIN()' or `MAX()':
 
           SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
 
    * The fields in `GROUP BY' do not refer to the beginning of the
      index:
 
           SELECT c1,c2 FROM t1 GROUP BY c2, c3;
 
    * The query refers to a key part that is after the `GROUP BY' parts,
      and for which there is no equality with a constant:
 
           SELECT c1,c3 FROM t1 GROUP BY c1, c2;
 
 

Info Catalog

(mysql.info.gz) GROUP BY optimization

(mysql.info.gz) GROUP BY optimization

(mysql.info.gz) Tight index scan