MySQL Reference Manual :: 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 reads 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 avoids sorting because searching with prefixes in an ordered index retrieves all keys in order.

The following queries do not work with the first method above, but still work with the second index access method (assuming we have the aforementioned index idx on table t1):