> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL GROUP BY performance and grouping at scale > Why a MySQL GROUP BY builds a temporary table, how an index removes it, what the same filter costs in WHERE against HAVING, and when ANY_VALUE is safe. > [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md) > > 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](https://villagesql.com/docs/en/tutorial/aggregate-functions.md), [GROUP BY](https://villagesql.com/docs/en/tutorial/group-by.md), [HAVING](https://villagesql.com/docs/en/tutorial/having.md), [COUNT DISTINCT](https://villagesql.com/docs/en/tutorial/count-distinct.md), [GROUP\_CONCAT](https://villagesql.com/docs/en/tutorial/group-concat.md) and [WITH ROLLUP](https://villagesql.com/docs/en/tutorial/with-rollup.md). 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: ```sql theme={null} EXPLAIN SELECT rating, COUNT(*) FROM film GROUP BY rating\G ``` ```text theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} EXPLAIN SELECT customer_id, COUNT(*) FROM payment GROUP BY customer_id\G ``` ```text theme={null} 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: ```sql theme={null} EXPLAIN FORMAT=TREE SELECT customer_id, COUNT(*) FROM payment WHERE customer_id <= 100 GROUP BY customer_id\G ``` ```text theme={null} -> 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: ```sql theme={null} EXPLAIN FORMAT=TREE SELECT customer_id, COUNT(*) FROM payment GROUP BY customer_id HAVING customer_id <= 100\G ``` ```text theme={null} -> 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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](https://villagesql.com/docs/en/guides/joins.md) 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: ```sql theme={null} SELECT customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS customer_total FROM payment; ``` [Window functions in MySQL 8.4](https://villagesql.com/docs/en/guides/window-functions.md) 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`. | ## See also * [Window functions in MySQL 8.4](https://villagesql.com/docs/en/guides/window-functions.md) — aggregating without collapsing the rows * [MySQL JOIN performance and common mistakes](https://villagesql.com/docs/en/guides/joins.md) — why a one-to-many join inflates a total * [When a MySQL CTE beats a derived table](https://villagesql.com/docs/en/guides/ctes-in-mysql.md) — pre-aggregating before the main query * [Common MySQL errors](https://villagesql.com/docs/en/guides/common-mysql-errors.md) — ERROR 1055 and ERROR 1111 in context * [GROUP BY](https://villagesql.com/docs/en/tutorial/group-by.md) — the tutorial, if you want the clause itself first This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.