How Indexes Enable Efficient GROUP BY Queries in MySQL

Because GROUP BY also needs to perform sorting, it shares similarities with ORDER BY; however, GROUP BY adds the step of grouping after sorting. Consequently, the implementation of GROUP BY can, like ORDER BY, leverage indexes.

For example, given an index idx(c1,c2,c3), the query:

SELECT c1, c2 FROM t1 WHERE c1 < 10 GROUP BY c1, c2;

can be satisfied directly by an index scan.

Conditions required to use an index scan for GROUP BY are:

(1) The query targets a single table.

(2) The GROUP BY columns must occupy the leading, contiguous positions of the same index.

(3) Any column referenced outside the GROUP BY that belongs to the index must appear as a constant.

(4) When aggregate functions are used together with GROUP BY, only MAX and MIN are allowed, and they must refer to the same column.

(5) If the GROUP BY columns are not a left‑most prefix of the index, the WHERE clause must contain the missing index key as a constant.

For instance, the query:

SELECT c1, c2 FROM t1 WHERE c2 = 10 GROUP BY c1, c3;

uses GROUP BY on c1 and c3, missing c2 from the index prefix. Because the WHERE clause provides c2 as a constant, the condition satisfies the left‑most prefix rule, allowing the index to fulfill the GROUP BY operation.