Using temporary in MySQL EXPLAIN

What it does

Using temporary in the Extra column means MySQL creates an internal temporary table to hold intermediate results. For GROUP BY, it keeps one row per group in that table and updates it as rows arrive (a count, a sum), then reads the table back to produce the result.

EXPLAIN ANALYZE shows it as two steps:

-> Table scan on <temporary>
    -> Aggregate using temporary table

The table is held in memory by the TempTable engine. If one grows past tmp_table_size (16 MB by default on MySQL 8.4), it becomes an InnoDB table on disk, which is much slower.

When the planner picks it

When rows can’t be processed in the order the query needs them. Typical causes:

• GROUP BY on columns that no index used by the query returns in order, so groups arrive mixed up
• GROUP BY and ORDER BY on different columns or directions (often with Using filesort as well)
• DISTINCT combined with ORDER BY, GROUP BY on an expression such as DATE(created_at), UNION (without ALL), COUNT(DISTINCT …), and some joins where the grouped column comes from a table that isn’t read first

In a join, Using temporary appears on the row of the first table in the plan, not on the table whose column is grouped: … FROM customers c JOIN orders o … GROUP BY o.status showed it on the c row.

The traditional EXPLAIN doesn’t report every temporary table: derived tables, materialised subqueries and window functions can use one without this note.

Reading its numbers

The note itself has no numbers. In EXPLAIN ANALYZE:

• rows= on Aggregate using temporary table and Table scan on <temporary> is the number of rows in the temporary table, which for GROUP BY is the number of groups.
• The line below shows how many rows went in, and how long getting them took. The difference between its actual time and the aggregate’s is the cost of the temporary table itself.

To see whether one went to disk, compare your session’s counters before and after the query:

SHOW SESSION STATUS LIKE 'Created_tmp%';

Created_tmp_tables counts every internal temporary table; Created_tmp_disk_tables counts those that ended up on disk.

When it’s a problem

A temporary table of a few hundred groups held in memory is cheap. Look closer when:

• Created_tmp_disk_tables rises: the table outgrew memory. Group fewer rows (filter earlier), select fewer or narrower columns, or give the query an index so no temporary table is needed.
• Many rows go in and the query runs often: an index that returns rows already in GROUP BY order lets MySQL aggregate as rows stream past (Group aggregate in the plan), with no table. Put the WHERE equality columns first, then the GROUP BY columns; add the aggregated columns too and the index covers the query.

But check the replacement plan. In the example below, removing a temporary table made a query five times slower.

Example

On MySQL 8.4.11, an orders table with 500,000 rows:

CREATE TABLE orders (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id int NOT NULL,
  status varchar(20) NOT NULL,
  total decimal(10,2) NOT NULL,
  created_at datetime NOT NULL,
  note varchar(100) NULL,
  KEY idx_customer (customer_id),
  KEY idx_status_created (status, created_at)
);
-- 500,000 rows; 30% have status 'paid'

Spend per customer, for paid orders:

EXPLAIN SELECT customer_id, SUM(total) AS spent
FROM orders WHERE status = 'paid' GROUP BY customer_id;

+----+-------------+--------+------------+------+---------------------------------+--------------------+---------+-------+--------+----------+-----------------+
| id | select_type | table  | partitions | type | possible_keys                   | key                | key_len | ref   | rows   | filtered | Extra           |
+----+-------------+--------+------------+------+---------------------------------+--------------------+---------+-------+--------+----------+-----------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_customer,idx_status_created | idx_status_created | 82      | const | 249444 |   100.00 | Using temporary |
+----+-------------+--------+------------+------+---------------------------------+--------------------+---------+-------+--------+----------+-----------------+

-> Table scan on <temporary>  (actual time=228..229 rows=6000 loops=1)
    -> Aggregate using temporary table  (actual time=228..228 rows=6000 loops=1)
        -> Index lookup on orders using idx_status_created (status='paid')  (cost=26075 rows=249444) (actual time=9.75..195 rows=150000 loops=1)

150,000 paid orders go into a temporary table of 6,000 groups. The index on status finds them, but returns them in created_at order, not by customer. An index ordered by status, then customer, and holding total too, gives the rows in group order and covers the query:

CREATE INDEX idx_status_customer_total ON orders (status, customer_id, total);

+----+-------------+--------+------------+------+-----------------------------------------------------------+---------------------------+---------+-------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys                                             | key                       | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------------------------------------------+---------------------------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_customer,idx_status_created,idx_status_customer_total | idx_status_customer_total | 82      | const | 249444 |   100.00 | Using index |
+----+-------------+--------+------------+------+-----------------------------------------------------------+---------------------------+---------+-------+--------+----------+-------------+

-> Group aggregate: sum(orders.total)  (cost=51019 rows=19942) (actual time=0.264..34.3 rows=6000 loops=1)
    -> Covering index lookup on orders using idx_status_customer_total (status='paid')  (cost=26075 rows=249444) (actual time=0.23..25.1 rows=150000 loops=1)

No temporary table, and 229 ms became 34 ms.

Now orders per day, before any new index:

EXPLAIN ANALYZE SELECT DATE(created_at) AS day, COUNT(*) FROM orders GROUP BY day;

-> Table scan on <temporary>  (actual time=124..124 rows=640 loops=1)
    -> Aggregate using temporary table  (actual time=124..124 rows=640 loops=1)
        -> Covering index scan on orders using idx_status_created  (cost=50266 rows=498888) (actual time=0.134..51.5 rows=500000 loops=1)

SHOW SESSION STATUS LIKE 'Created_tmp%' went from Created_tmp_tables 0 to 1, with Created_tmp_disk_tables still 0: 640 groups, in memory. A functional index on DATE(created_at) does remove the temporary table:

ALTER TABLE orders ADD INDEX idx_created_day ((DATE(created_at)));

-> Group aggregate: count(0)  (cost=100154 rows=644) (actual time=2.14..638 rows=640 loops=1)
    -> Index scan on orders using idx_created_day  (cost=50266 rows=498888) (actual time=0.633..617 rows=500000 loops=1)

and the query got five times slower, 124 ms to 638 ms, because walking that index means fetching each full row, while the first plan read a compact covering index. Keep the temporary table.

In Inlet

Run EXPLAIN or EXPLAIN ANALYZE in Inlet’s query editor like any other statement (⌘↩). With your own Anthropic API key, Ask Claude (⌘L) can explain a plan; it sends the schema and the plan, never rows. The structure editor shows the CREATE INDEX statement before it runs.