Internal Temporary Tables in MySQL: When a Query Spills to Disk

MySQL and MariaDB build internal temporary tables behind your back whenever a query needs scratch space. Most of them stay in memory. A few spill to disk, and you can see which ones with two simple tools.

A Table With 10,000 Orders

I ran everything here on MariaDB 12.3. The syntax, the status counters and EXPLAIN work the same way in MySQL, as its manual documents. MySQL 8 builds these tables with its own TempTable engine, so its memory settings have other names. MariaDB writes the disk version with its Aria engine.

The orders table below has 10,000 rows. A helper table of ten digits builds them: four copies joined together count from 0 to 9999. The note column is TEXT on purpose, because it matters later.

CREATE DATABASE sqla_tmptables;

USE sqla_tmptables;

CREATE TABLE digits (d INT PRIMARY KEY);

INSERT INTO digits VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  city VARCHAR(30) NOT NULL,
  status VARCHAR(10) NOT NULL,
  amount DECIMAL(8,2) NOT NULL,
  note TEXT
);

INSERT INTO orders (customer_id, city, status, amount, note)
SELECT n % 500 + 1,
       ELT(n % 4 + 1, 'Austin', 'Boston', 'Chicago', 'Denver'),
       ELT(n % 3 + 1, 'paid', 'open', 'void'),
       n % 97 + 10,
       CONCAT('note ', n % 50)
FROM (SELECT a.d + 10 * b.d + 100 * c.d + 1000 * e.d AS n
      FROM digits a, digits b, digits c, digits e) AS nums;

SELECT COUNT(*) AS order_rows FROM orders;

What the Server Is Doing

Sometimes a query needs scratch space. To count rows per city, for example, the server first collects the groups in a table. It creates that table, fills it, and drops it when the query ends. You never name it, which is why it’s called internal, unlike a table you make with CREATE TEMPORARY TABLE.

The table starts in memory. If it grows past a limit, the server copies it to disk and carries on there. A disk table is slower, so the spill is the event to catch.

Count Them With Status Counters

Two session counters track internal temporary tables. Created_tmp_tables counts every one, and Created_tmp_disk_tables counts the ones that reached the disk. FLUSH STATUS sets your session’s counters to zero, so run it before each test. Other connections never disturb your numbers, because the counters belong to your session alone.

FLUSH STATUS;

SELECT city, COUNT(*) AS orders FROM orders GROUP BY city;

SHOW SESSION STATUS LIKE 'Created_tmp%tables';

One temporary table, and none on disk. The city column has no index, so the server groups the rows in a scratch table. EXPLAIN shows the same plan without running the query.

EXPLAIN SELECT city, COUNT(*) AS orders FROM orders GROUP BY city;

Using temporary in the Extra column is the sign. Using filesort is a separate step: the server sorts the grouped rows.

Which Queries Build One

Several query shapes need a scratch table. DISTINCT with ORDER BY does, because the server removes duplicates and then sorts. UNION does, because it removes duplicate rows across its parts. A derived table with a GROUP BY does too. The server stores the inner result before the outer query reads it.

FLUSH STATUS;

SELECT DISTINCT status FROM orders ORDER BY status;

SHOW SESSION STATUS LIKE 'Created_tmp%tables';

EXPLAIN SELECT DISTINCT status FROM orders ORDER BY status;

FLUSH STATUS;

SELECT city FROM orders WHERE id <= 3 UNION SELECT city FROM orders WHERE id > 9997;

SHOW SESSION STATUS LIKE 'Created_tmp%tables';

EXPLAIN SELECT city FROM orders WHERE id <= 3 UNION SELECT city FROM orders WHERE id > 9997;

UNION ALL is the same query without the duplicate removal. The derived table below wraps our city count in a second query.

FLUSH STATUS;

SELECT city FROM orders WHERE id <= 3 UNION ALL SELECT city FROM orders WHERE id > 9997;

SHOW SESSION STATUS LIKE 'Created_tmp%tables';

EXPLAIN SELECT city FROM orders WHERE id <= 3 UNION ALL SELECT city FROM orders WHERE id > 9997;

FLUSH STATUS;

SELECT g.city, g.n FROM (SELECT city, COUNT(*) AS n FROM orders GROUP BY city) AS g WHERE g.n > 2000;

SHOW SESSION STATUS LIKE 'Created_tmp%tables';

EXPLAIN SELECT g.city, g.n FROM (SELECT city, COUNT(*) AS n FROM orders GROUP BY city) AS g WHERE g.n > 2000;

UNION returned 4 rows and UNION ALL returned 6, so the duplicates are what the table removes. UNION ALL compares nothing, so it builds nothing. MariaDB prints no Using temporary text for a UNION. The row named is the table.

The derived table shows 2. The grouping inside it needs one, and the stored result of the derived table is the other.

When a Table Goes to Disk

Two settings cap the size of an in-memory table: tmp_table_size and max_heap_table_size. The smaller one applies. Here both are 16 MB.

SELECT @@tmp_table_size, @@max_heap_table_size;

To force a spill without a giant table, lower the limit for your session only. SET SESSION touches nothing else on the server. The next query groups on two columns, so it builds one group per order.

SELECT COUNT(*) AS group_count FROM (SELECT 1 FROM orders GROUP BY customer_id, amount) AS g;

Ten thousand groups do not fit in 128 KB. LIMIT 3 only keeps the printout short.

SET SESSION tmp_table_size = 131072;

FLUSH STATUS;

SELECT customer_id, amount, COUNT(*) AS n FROM orders GROUP BY customer_id, amount LIMIT 3;

SHOW SESSION STATUS LIKE 'Created_tmp%tables';

SET SESSION tmp_table_size = DEFAULT;

The scratch table reached the disk. Now the same test with only the other setting lowered, and then with both at their defaults.

SET SESSION max_heap_table_size = 131072;

FLUSH STATUS;

SELECT customer_id, amount, COUNT(*) AS n FROM orders GROUP BY customer_id, amount LIMIT 3;

SHOW SESSION STATUS LIKE 'Created_tmp%tables';

SET SESSION max_heap_table_size = DEFAULT;

FLUSH STATUS;

SELECT customer_id, amount, COUNT(*) AS n FROM orders GROUP BY customer_id, amount LIMIT 3;

SHOW SESSION STATUS LIKE 'Created_tmp%tables';

Lowering either setting made the table spill, and with both at 16 MB it stayed in memory. Each temporary table can grow to the limit. A large server-wide value adds up when many connections build big ones. Raising the limits is the tempting fix. It hides the spill but leaves the query as it was. An index or a shorter SELECT list removes the cause.

TEXT Columns Skip Memory

Some column types can’t live in a memory temporary table, and TEXT and BLOB are the usual ones. A query that puts a TEXT value into its scratch table goes to disk at once, whatever the limit. Here are three queries at the default 16 MB.

FLUSH STATUS;

SELECT note, COUNT(*) AS n FROM orders GROUP BY note LIMIT 3;

SHOW SESSION STATUS LIKE 'Created_tmp%tables';

FLUSH STATUS;

SELECT status, MAX(CAST(note AS CHAR(20))) AS top_note FROM orders GROUP BY status;

SHOW SESSION STATUS LIKE 'Created_tmp%tables';

FLUSH STATUS;

SELECT status, MAX(note) AS top_note FROM orders GROUP BY status;

SHOW SESSION STATUS LIKE 'Created_tmp%tables';

The note column has only 50 distinct values and the limit is 16 MB. Even so, two of these tables went to disk. The TEXT type forced it. The cast to a short CHAR kept the table in memory. Leave TEXT columns out of grouped and DISTINCT queries when you can.

An Index Removes the Table

The best fix is to stop needing the table. An index on city hands the server the rows already in order, so it counts each group as it reads.

CREATE INDEX idx_city ON orders (city);

FLUSH STATUS;

SELECT city, COUNT(*) AS orders FROM orders GROUP BY city;

SHOW SESSION STATUS LIKE 'Created_tmp%tables';

EXPLAIN SELECT city, COUNT(*) AS orders FROM orders GROUP BY city;

The same query as before now shows no Using temporary and no counter change. An index has a price, because every insert and update must maintain it. Add one for queries that run many times a day, not for every query.

Do They Matter?

You could say internal temporary tables are harmless. Fair point. Most of the queries above made one without touching the disk, and the server dropped each at the end. The cost shows up when a table spills, or when a query that builds one runs thousands of times. Check Created_tmp_disk_tables first, and look at the total after that. For a whole server, SHOW GLOBAL STATUS LIKE ‘Created_tmp%tables’ gives totals since startup. A disk count that keeps growing points to queries worth tuning.

A Short Checklist

• Run FLUSH STATUS, then your query, then SHOW SESSION STATUS LIKE ‘Created_tmp%tables’.
• Read Created_tmp_disk_tables first, then Created_tmp_tables.
• Look for Using temporary in EXPLAIN, and for a UNION RESULT row.
• Use UNION ALL when duplicate rows are fine.
• Keep TEXT and BLOB columns out of grouped and DISTINCT queries.
• Add an index that matches the GROUP BY column.
• Raise tmp_table_size and max_heap_table_size together, and only after the other fixes.

When you finish testing, drop the example database. That removes both tables and the index.

DROP DATABASE sqla_tmptables;

Finding internal temporary tables is not a fault, it is a message about the shape of your query.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.