Optimizing MySQL GROUP BY with Index Scans: Loose vs Tight Index Scan

In a production environment a GROUP BY query that originally took 3 seconds was rewritten to finish in 30 ms by replacing a simple aggregation with a join that leverages the primary key and a filtered index.

SELECT taskUniqueId, max(reportTime) AS reportTime
FROM task_log_info
WHERE reportTime > '2024-04-07'
GROUP BY taskUniqueId;

SELECT a.taskUniqueId, reportTime
FROM task_log_info a
JOIN (
    SELECT taskUniqueId, max(id) AS id
    FROM task_log_info
    GROUP BY taskUniqueId
) tmp ON a.id = tmp.id
WHERE reportTime >= '2024-04-07';

The two statements use the same index but differ greatly in performance; the optimized version avoids a temporary table and filesort.

The article then prepares a test table t2 with a composite index c1_c2_c3_idx and populates it with representative data.

CREATE TABLE t2 (
  id INT AUTO_INCREMENT,
  c1 CHAR(64) NOT NULL,
  c2 CHAR(64) NOT NULL,
  c3 CHAR(64) NOT NULL,
  c4 CHAR(64) NOT NULL,
  PRIMARY KEY(id),
  KEY c1_c2_c3_idx (c1, c2, c3)
) ENGINE=INNODB;

INSERT INTO t2 VALUES
  (null,'a','b','a','a'),
  (null,'a','b','a','a'),
  ... ;
ANALYZE TABLE t2;

Two scenarios are examined:

When the table has no suitable index, MySQL must create a temporary table for GROUP BY, resulting in Using temporary and often Using filesort .

When an index exists, MySQL can employ different scanning algorithms:

Loose Index Scan

Also called “Loose Index Scan”, this method jumps over index entries, scanning only the first (or last) row of each group. It appears in the execution plan as type=range with Extra: Using index for group‑by .

EXPLAIN SELECT c1, MIN(c2) FROM t2 GROUP BY c1;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | c1_c2_c3_idx  | c1_c2_c3_idx | 256     | NULL |    7 | 100.00   | Using index for group‑by |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+

Loose scans are ideal when each group needs only a single row (e.g., MIN/MAX) and the grouping columns satisfy the left‑most index rule.

Tight Index Scan

When the optimizer cannot skip rows, it performs a full or range scan of the index, shown as type=index with Extra: Using index . This still avoids a temporary table but may read many more rows.

EXPLAIN SELECT c1, COUNT(*) FROM t2 GROUP BY c1;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | index | c1_c2_c3_idx  | c1_c2_c3_idx | 768     | NULL |   24 | 100.00   | Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+

Combined Scan

For some aggregation queries (e.g., COUNT(DISTINCT)), the optimizer may use a hybrid approach, reported as Extra: Using index for group‑by (scanning) . This blends the benefits of both scans.

EXPLAIN SELECT c1, COUNT(DISTINCT c2,c3) FROM t2 GROUP BY c1;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                               |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | c1_c2_c3_idx  | c1_c2_c3_idx | 512     | NULL |   16 | 100.00   | Using index for group‑by (scanning) |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------------------------------+

The article lists the conditions required for a Loose Index Scan: single‑table query, GROUP BY columns follow the left‑most index rule, all aggregated columns are covered by the index, and the index must be a full‑column index (no prefix).

Cost analysis shows that Loose Index Scan reduces the number of rows read when groups are few and each group contains many rows; however, if groups are many and each contains few rows, Tight Index Scan or the combined method may be cheaper.

Finally, the original case’s execution plans before and after optimization are shown, confirming that the transformation from a tight scan to a loose scan yields the dramatic performance gain.

References include MySQL official documentation on GROUP BY optimization, blog posts about Loose Index Scan, and several Chinese technical articles.