Skip navigation links
7.2.11.2 Tight index scan »
Section Navigation      [Toggle]

7.2.11.1. Loose index scan

The most efficient way is when the index is used to directly retrieve the group fields. With this access method, MySQL uses the property of some index types (for example, B-Trees) that the keys are ordered. This property allows use of lookup groups in an index without having to consider all keys in the index that satisfy all WHERE conditions. Since this access method considers only a fraction of the keys in an index, 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 in Sección 7.2.1, “Sintaxis de EXPLAIN (Obtener información acerca de un SELECT)” of the range join type), 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, then 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 provide several examples that fall into this category, assuming 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 other aggregate functions 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;

Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.