MySQL GROUP BY Performance: Indexes, Temporary Tables

A GROUP BY on a big table can be slow. MySQL must bring the rows of each group together. It does it in one of two ways.

With an index. If an index returns the rows already in group order, MySQL reads them in order and finishes a group when the value changes. No extra work.
With a temporary table. Otherwise MySQL collects the groups in a temporary table (in memory, or on disk when large) and updates the counters for each row.

query.sql

SQL

SET SESSION cte_max_recursion_depth = 20000;

CREATE TABLE page_views (
    id INT AUTO_INCREMENT PRIMARY KEY,
    page_id INT NOT NULL,
    viewed_on DATE NOT NULL,
    country CHAR(2) NOT NULL
);
INSERT INTO page_views (page_id, viewed_on, country)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT 1 + MOD(i * 7, 50), DATE_ADD('2026-01-01', INTERVAL MOD(i, 90) DAY), ELT(1 + MOD(i, 4), 'IN', 'US', 'DE', 'BR') FROM n;
ANALYZE TABLE page_views;

Without an index

query.sql

SQL

EXPLAIN FORMAT=TRADITIONAL SELECT page_id, COUNT(*) FROM page_views GROUP BY page_id;

Output

OUTPUT

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra           |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
|  1 | SIMPLE      | page_views | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10016 |   100.00 | Using temporary |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
1 row in set

type = ALL reads the whole table, and Using temporary means a temporary table collects the groups. With 10,000 rows it is quick. With 500 million rows it is a problem.

With an index on the grouping column

query.sql

SQL

1
2

CREATE INDEX idx_page ON page_views (page_id);
EXPLAIN FORMAT=TRADITIONAL SELECT page_id, COUNT(*) FROM page_views GROUP BY page_id;

Output

OUTPUT

+----+-------------+------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | page_views | NULL       | index | idx_page      | idx_page | 4       | NULL | 10016 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+-------+----------+-------------+
1 row in set

The temporary table is gone. MySQL reads idx_page in order, and counts. This is called a tight index scan. (When only MIN or MAX are asked, MySQL can even skip through the index group by group, a loose index scan, shown as Using index for group-by.)

Filter and group: a composite index

query.sql

SQL

EXPLAIN FORMAT=TRADITIONAL SELECT country, COUNT(*) FROM page_views WHERE viewed_on >= '2026-02-01' AND viewed_on < '2026-03-01' GROUP BY country;

Output

OUTPUT

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                        |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+------------------------------+
|  1 | SIMPLE      | page_views | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10016 |    11.11 | Using where; Using temporary |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+------------------------------+
1 row in set

query.sql

SQL

1
2

CREATE INDEX idx_date_country ON page_views (viewed_on, country);
EXPLAIN FORMAT=TRADITIONAL SELECT country, COUNT(*) FROM page_views WHERE viewed_on >= '2026-02-01' AND viewed_on < '2026-03-01' GROUP BY country;

Output

OUTPUT

+----+-------------+------------+------------+-------+------------------+------------------+---------+------+------+----------+-------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                     |
+----+-------------+------------+------------+-------+------------------+------------------+---------+------+------+----------+-------------------------------------------+
|  1 | SIMPLE      | page_views | NULL       | range | idx_date_country | idx_date_country | 3       | NULL | 3108 |   100.00 | Using where; Using index; Using temporary |
+----+-------------+------------+------------+-------+------------------+------------------+---------+------+------+----------+-------------------------------------------+
1 row in set

The index finds the date range, and since it also contains country, the query is answered from the index alone (Using index). A covering index like this is one of the best tools for reports (Covering Indexes). Note that it still needs a temporary table for the country groups, because the index is ordered by date first. Putting the equality column first and the range column last would help in other queries.

What you can do about a slow GROUP BY

Filter first. A WHERE that cuts the rows (a date range) is the biggest win.
Index the grouping columns, or a composite index with the filter columns first and the grouping columns after.
Group fewer rows. Aggregate a subset in a derived table, then join.
Select only what you need. Wide rows make the temporary table larger.
Avoid DISTINCT aggregates on huge sets if an approximate number will do.
Pre-compute. Build a summary table, updated by a scheduled job or a trigger:

query.sql

SQL

CREATE TABLE daily_page_views (
    viewed_on DATE NOT NULL,
    page_id INT NOT NULL,
    views INT NOT NULL,
    PRIMARY KEY (viewed_on, page_id)
);

INSERT INTO daily_page_views (viewed_on, page_id, views)
SELECT * FROM (SELECT viewed_on, page_id, COUNT(*) AS views FROM page_views GROUP BY viewed_on, page_id) AS new_rows
ON DUPLICATE KEY UPDATE views = new_rows.views;

SELECT COUNT(*) AS summary_rows FROM daily_page_views;

Output

OUTPUT

+--------------+
| summary_rows |
+--------------+
|          450 |
+--------------+
1 row in set

A dashboard that reads daily_page_views touches a few thousand rows instead of hundreds of millions. See Summary Tables.

Temporary tables on disk

Large groupings may spill to disk. MySQL 8 uses the TempTable engine in memory first (limited by temptable_max_ram, 1 GB by default) and then on-disk InnoDB temporary tables. Watch Created_tmp_disk_tables in SHOW GLOBAL STATUS. A growing value means groupings and sorts are too large for memory, and the better fix is usually an index or a smaller result, not a bigger memory setting.

Do not group when you need only one row

SELECT MAX(x) FROM t WHERE id = 5 needs no GROUP BY. And checking “does one exist?” with COUNT(*) > 0 is slower than EXISTS.

lightbulb

Look at EXPLAIN

Using temporary and Using filesort on a large table are a signal to look for an index. On small tables, ignore them.