MySQL Optimized the GROUP BY scheme

The most 1-like way to perform the GROUP BY clause is to scan the entire table, then create a new temporary table in which all rows for each group should be contiguous, and finally use the temporary table to find the group and apply aggregation functions (if any). In some cases, MySQL accesses the index to get the result without creating a temporary table. The EXPLAIN output for such a query shows the value of the Extra column as Using index for ES9en-ES10en.

1. Loose index scan

1. Meet the conditions

The query is for 1 table.
GROUP BY USES the leftmost prefix of the index.
You can only use the MIN() and MAX() aggregation functions, and they all point to the same column.
Example 2.

Table t1 (c1, c2 c3, c4) with one index idx (c1 c2, c3) :

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 ; 

Examples of unqualified conditions:

1. In addition to MIN() or MAX(), there are other cumulative functions, such as:

SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

2. The fields in the GROUP BY clause do not refer to the beginning of an index, for example:

SELECT c1,c2 FROM t1 GROUP BY c2, c3;

3. The query references part 1 of the keyword after the GROUP BY section and does not have an equation equal to a constant, such as:

SELECT c1,c3 FROM t1 GROUP BY c1, c2 ; 

2. Compact index scan

If the loose index scan condition is not met, GROUP BY can still be executed without creating a temporary table. If there are range conditions in the WHERE clause, the method reads only the keywords that satisfy those conditions.

Otherwise, index scans are performed. This method reads the range defined by the WHERE clause.

1. There is a vulnerability in GROUP BY, but it has been overridden by the condition c2 = 'a'.

SELECT c1 . c2 . c3 FROM t1 WHERE c2 = 'a' GROUP BY c1 . c3;

GROUP BY does not satisfy the leftmost prefix, but there is a condition that provides the element's constant:

SELECT c1 . c2 . c3 FROM t1 WHERE c1 = 'a' GROUP BY c2 . c3;