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
still may be 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. 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.
Assume that there is an index idx(c1,c2,c3)
on table t1(c1,c2,c3,c4)
. 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.
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;
User Comments
Add your own comment.