MySQL GROUP BY performance and grouping at scale

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

A GROUP BY that returns in milliseconds on a test table can take minutes on a real one, and the query text is identical. The difference is whether the server can walk an index in group order or has to build a temporary table first. This guide is about that difference, and about the choices that decide which you get. If you are learning the clauses themselves, the tutorial teaches them against a sample database: aggregate functions, GROUP BY, HAVING, COUNT DISTINCT, GROUP_CONCAT and WITH ROLLUP. Every query and plan on this page came from 1 server against the Sakila sample database. Costs and row estimates come from InnoDB’s sampled statistics and from your optimizer_switch settings, so your numbers will differ. The shape of each plan is the part that transfers.

Grouping without an index builds a temporary table

To group rows, the server has to bring the rows of each group together. With no index on the grouping column it does that by building a temporary table:

EXPLAIN SELECT rating, COUNT(*) FROM film GROUP BY rating\G

         type: ALL
possible_keys: NULL
          key: NULL
         rows: 1000
        Extra: Using temporary

Using temporary is the line to look for. On 1,000 rows it costs nothing. The table is held in memory while it fits, and spills to disk when it does not, which is where a grouped report stops being fast. Which setting caps that memory depends on the engine the server uses for internal temporary tables. Check yours before you change anything:

SELECT @@internal_tmp_mem_storage_engine AS engine,
       @@temptable_max_ram, @@temptable_max_mmap, @@tmp_table_size;

They do different jobs and all of them apply under TempTable. temptable_max_ram and temptable_max_mmap cap what the engine may hold across all sessions. tmp_table_size caps each individual internal temporary table: at 1 KB a grouped query over 1,000 rows took Created_tmp_disk_tables from 0 to 1, and at 64 MB the same query stayed in memory. Watch that counter rather than guessing:

SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';

An index on the grouping column removes the step entirely, because the index is already in that order:

EXPLAIN SELECT customer_id, COUNT(*) FROM payment GROUP BY customer_id\G

         type: index
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
         rows: 16500
        Extra: Using index

No Using temporary and no Using filesort. The server reads the index in order, counts each run of equal values, and emits a row. Using index means it never touched the table itself. So the first thing to check on a slow GROUP BY is whether an index leads with the grouping columns, in the same order you grouped them. A composite index on (a, b) serves GROUP BY a and GROUP BY a, b. For GROUP BY b the server can still scan it as a covering index, reported as type: index with Using index, but the rows arrive out of group order so Using temporary comes back with them.

WHERE and HAVING are not a style choice

Both can express a filter. They cost differently, because WHERE runs before the grouping and HAVING runs after it. A condition on the grouping column itself can be written either way and means the same thing, which makes it the honest comparison. In WHERE, the server narrows the index range first and groups only what it read:

EXPLAIN FORMAT=TREE SELECT customer_id, COUNT(*) FROM payment WHERE customer_id <= 100 GROUP BY customer_id\G

-> Group aggregate: count(0)  (cost=814 rows=599)
    -> Filter: (payment.customer_id <= 100)  (cost=543 rows=2710)
        -> Covering index range scan on payment using idx_fk_customer_id over (customer_id <= 100)  (cost=543 rows=2710)

In HAVING, the same restriction arrives too late to narrow anything:

EXPLAIN FORMAT=TREE SELECT customer_id, COUNT(*) FROM payment GROUP BY customer_id HAVING customer_id <= 100\G

-> Filter: (payment.customer_id <= 100)  (cost=3324 rows=599)
    -> Group aggregate: count(0)  (cost=3324 rows=599)
        -> Covering index scan on payment using idx_fk_customer_id  (cost=1674 rows=16500)

Same rows out, 4 times the estimated cost. The WHERE version reads an estimated 2,710 index entries; the HAVING version reads the whole index and throws most of the groups away afterwards. Those estimates come from InnoDB’s sampled statistics and from the cost constants in mysql.server_cost, so your numbers will differ from these. The shape of the 2 plans is the part that transfers. The rule: put a condition in WHERE whenever it tests a row. Use HAVING only for what tests a group, which in practice means anything containing an aggregate. A HAVING clause with no aggregate in it is nearly always a WHERE clause in the wrong place, and WHERE with an aggregate in it is ERROR 1111 (HY000): Invalid use of group function.

ONLY_FULL_GROUP_BY, and the escape hatch

only_full_group_by is on by default and rejects a query that selects a column which is neither grouped nor aggregated, with ERROR 1055. The reason is that such a query has no defined answer: the group holds many rows and nothing says which one to show. ANY_VALUE() tells the server you accept an arbitrary one, and the query then runs:

SELECT rating, ANY_VALUE(title) AS a_title, COUNT(*) AS films
FROM film
GROUP BY rating
ORDER BY rating;

No output is shown here on purpose. Which title comes back is not defined, so any result printed on this page would be a value you cannot reproduce. Use ANY_VALUE() when the column is functionally dependent on the grouping column and the server cannot prove it, which is the legitimate case. Do not use it to silence the error on a column that genuinely varies within the group: the value you get is not the first, the smallest, or anything else you can rely on, and it can change when the plan changes. MIN() or MAX() says what you meant and costs the same. Turning only_full_group_by off globally converts every one of these errors into a wrong answer nobody sees. It is the wrong fix. The check sees through an expression built on a grouped column, so SELECT COALESCE(rating, 'unrated') ... GROUP BY rating is accepted. It does not see through an expression built on a grouped expression. Group by DATE_FORMAT(payment_date, '%Y-%m') and select COALESCE(DATE_FORMAT(payment_date, '%Y-%m'), 'ALL'), and the same ERROR 1055 names payment_date, because the server compares the 2 expressions rather than the columns inside them. That is the common reporting case, and the way out is to group in a derived table and wrap the result outside it:

SELECT COALESCE(month, 'ALL') AS month, payments
FROM (SELECT DATE_FORMAT(payment_date, '%Y-%m') AS month, COUNT(*) AS payments
      FROM payment GROUP BY DATE_FORMAT(payment_date, '%Y-%m')) AS m
ORDER BY month;

Aggregating a one-to-many join

Joining before grouping multiplies rows, so a SUM over the result double counts. The symptom is a total that is too high by a factor nobody can explain. Aggregate each side in its own subquery and join the results, rather than joining first and grouping afterwards:

SELECT c.last_name, p.payments, r.rentals
FROM customer AS c
JOIN (SELECT customer_id, COUNT(*) AS payments FROM payment GROUP BY customer_id) AS p
  ON p.customer_id = c.customer_id
JOIN (SELECT customer_id, COUNT(*) AS rentals FROM rental GROUP BY customer_id) AS r
  ON r.customer_id = c.customer_id
ORDER BY c.last_name
LIMIT 5;

Each subquery’s aggregate is over the rows it belongs to. Joining both tables first and grouping afterwards multiplies payments by rentals and inflates both counts. MySQL JOIN performance and common mistakes covers the multiplication itself.

When you do not want the rows collapsed

GROUP BY replaces the rows with 1 row per group. When you want the aggregate beside each row, a window function does that without collapsing anything, and usually without the temporary table:

SELECT customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM payment;

Window functions in MySQL 8.4 covers them properly.

Troubleshooting

Problem	Cause and fix
`GROUP BY` is slow	No index leads with the grouping columns. Check `EXPLAIN` for `Using temporary`.
Slow, and the disk is busy	The temporary table outgrew its memory budget and spilled. Narrow the rows first, then raise `tmp_table_size` for the single table or `temptable_max_ram` for the server’s total.
`ERROR 1055`	A selected column is neither grouped nor aggregated. Group it, aggregate it, or drop it.
A `HAVING` filter is slow	It has no aggregate in it, so it belongs in `WHERE`.
A total is too high	A one-to-many join multiplied the rows before the grouping.
`GROUP_CONCAT` result cut short	It hit `group_concat_max_len`, which warns rather than errors.
Groups come back in a surprising order	`GROUP BY` promises no order. Add `ORDER BY`.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

Grouping without an index builds a temporary table

WHERE and HAVING are not a style choice

ONLY_FULL_GROUP_BY, and the escape hatch

Aggregating a one-to-many join

When you do not want the rows collapsed

Troubleshooting

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​Grouping without an index builds a temporary table

​WHERE and HAVING are not a style choice

​ONLY_FULL_GROUP_BY, and the escape hatch

​Aggregating a one-to-many join

​When you do not want the rows collapsed

​Troubleshooting

​See also

Grouping without an index builds a temporary table

WHERE and HAVING are not a style choice

ONLY_FULL_GROUP_BY, and the escape hatch

Aggregating a one-to-many join

When you do not want the rows collapsed

Troubleshooting

See also