(mysql.info.gz) Tight index scan

Info Catalog

(mysql.info.gz) Loose index scan

(mysql.info.gz) GROUP BY optimization

 
 7.2.11.2 Tight index scan
 .........................
 
 A tight index scan may be either a full index scan or a range index
 scan, depending on the query conditions.
 
 When the conditions for a loose index scan are not met, it is still
 possible to avoid creation of temporary tables for `GROUP BY' queries.
 If there are range conditions in the `WHERE' clause, this method will
 read only the keys that satisfy these conditions. Otherwise, it
 performs an index scan. Since this method reads all keys in each range
 defined by the `WHERE' clause, or scans the whole index if there are no
 range conditions, we term it a "tight index scan." Notice that with a
 tight index scan, the grouping operation is performed after all keys
 that satisfy the range conditions have been found.
 
 For this method to work, it is sufficient that for all columns in a
 query referring to key parts before or in between the `GROUP BY' key
 parts, there is a constant equality condition. The constants from the
 equality conditions fill in the "gaps" in the search keys so that it is
 possible to form complete prefixes of the index. Then these index
 prefixes can be used for index lookups. If we require sorting of the
 `GROUP BY' result, and it is possible to form search keys that are
 prefixes of the index, MySQL also will avoid sorting because searching
 with prefixes in an ordered index retrieves all keys in order.
 
 The following queries will not work with the first method above, but
 will still work with the second index access method (assuming we have
 the aforementioned index `idx' on table `t1'):
 
    * There is a "gap" in `GROUP BY', but it is covered by the condition
      (c2 = 'a').
 
           SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
 
    * `GROUP BY' does not begin from the first key part, but there is a
      condition that provides a constant for that key part:
 
           SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
 
 

Info Catalog

(mysql.info.gz) Loose index scan

(mysql.info.gz) GROUP BY optimization