Why MySQL GROUP BY Can Be Slow and How to Optimize It

1. Introduction

First we create an employee table db_staff with fields for staff ID, identity number, name, email, age, sex, address, creation time and update time.

CREATE TABLE `db_staff` (
  `staff_id` int(8) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `id_no` varchar(20) DEFAULT NULL COMMENT '员工编号',
  `name` varchar(20) DEFAULT NULL COMMENT '员工姓名',
  `email` varchar(200) DEFAULT NULL COMMENT '邮件地址',
  `age` tinyint(3) DEFAULT NULL COMMENT '年龄',
  `sex` tinyint(1) DEFAULT '0' COMMENT '0:男 1:女',
  `address` varchar(300) DEFAULT NULL COMMENT '家庭住址',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`staff_id`),
  KEY `union_idno_name_email` (`id_no`,`name`,`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

Then we insert 20 rows of sample data.

2. Query analysis

We run a simple GROUP BY query to count employees by age:

SELECT age, COUNT(age) ca FROM db_staff GROUP BY age;

The result shows the number of employees per age.

The execution plan reveals that the query does not use an index and relies on a temporary table and filesort, which slows down execution.

Note:

filesort

in MySQL refers to any sort operation that does not use an index, even if it happens entirely in memory.

3. How to optimize

3.1 Remove unnecessary sorting

Appending ORDER BY NULL disables the implicit sorting performed by GROUP BY in versions prior to MySQL 8.0.

SELECT age, COUNT(age) ca FROM db_staff GROUP BY age ORDER BY NULL;

After this change the execution plan no longer shows Using filesort.

3.2 Use an index on the grouping column

Creating an index on age allows MySQL to satisfy the GROUP BY using the index, eliminating the temporary table and filesort.

ALTER TABLE db_staff ADD KEY age_idx(age);

3.3 Adjust buffer sizes to avoid disk temporary tables

If sort_buffer_size or tmp_table_size are insufficient, MySQL falls back to disk‑based temporary tables, which degrades performance. Increasing these buffers or reducing the result set can mitigate the issue.

3.4 Application‑level grouping

For very complex aggregations, consider fetching data in batches and performing the grouping in application code using multithreading.

3.5 Materialized (physical) views

For extremely large tables, a materialized view can pre‑aggregate data, reducing the load on the database, though it adds maintenance overhead and may have stale data.

4. Summary

The GROUP BY clause is convenient for aggregation, but without a covering index it can become a performance bottleneck on large tables. Applying the techniques above—removing unnecessary sorting, indexing the grouping column, tuning buffers, or offloading work to the application—can dramatically improve query speed.