7.2.13.1. Loose index scan
The most efficient way to process GROUP BY
is when the index is used to directly retrieve the group
fields. With this access method, MySQL uses the property of
some index types that the keys are ordered (for example,
BTREE). This property enables use of lookup
groups in an index without having to consider all keys in the
index that satisfy all WHERE conditions.
This access method considers only a fraction of the keys in an
index, so it is called a loose index
scan. When there is no WHERE
clause, a loose index scan reads as many keys as the number of
groups, which may be a much smaller number than that of all
keys. If the WHERE clause contains range
predicates (see the discussion of the range
join type in Section 7.2.1, “Optimizing Queries with EXPLAIN”), 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, 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 parts of the index than those from the
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 fall into this category, assuming that
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 aggregate functions other than
MIN() or MAX(), for
example:
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
-
The fields in the GROUP BY clause do
not refer to the beginning of the index, as shown here:
SELECT c1,c2 FROM t1 GROUP BY c2, c3;
-
The query refers to a part of a key that comes after the
GROUP BY part, and for which there is
no equality with a constant, an example being:
SELECT c1,c3 FROM t1 GROUP BY c1, c2;
