(mysql.info) tight-index-scan

Info Catalog

(mysql.info) loose-index-scan

(mysql.info) group-by-optimization

 
 7.2.13.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 reads
 only the keys that satisfy these conditions. Otherwise, it performs an
 index scan. Because 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 only after all keys
 that satisfy the range conditions have been found.
 
 For this method to work, it is sufficient that there is a constant
 equality condition for all columns in a query referring to parts of the
 key coming before or in between parts of the `GROUP BY' key. The
 constants from the equality conditions fill in any `gaps' in the search
 keys so that it is possible to form complete prefixes of the index.
 These index prefixes then 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 avoids extra sorting
 operations because searching with prefixes in an ordered index already
 retrieves all the keys in order.
 
 The following queries do not work with the loose index scan access
 method described earlier, but still work with the tight index scan
 access method (assuming that there is an index `idx(c1,c2,c3)' on table
 `t1(c1,c2,c3,c4)').
 
    * There is a gap in the `GROUP BY', but it is covered by the
      condition `c2 = 'a'':
 
           SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
 
    * The `GROUP BY' does not begin with the first part of the key, but
      there is a condition that provides a constant for that part:
 
           SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
 

Info Catalog

(mysql.info) loose-index-scan

(mysql.info) group-by-optimization