Posted inMySQL

Optimizing Temporary Tables in MySQL: Preventing I/O Bottlenecks When Processing Complex Queries

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

A 30-Second Query That Freezes the Server — More Common Than You Think

The first time I ran into this issue was when a report query on an e-commerce system started taking 30–40 seconds to run. The MySQL server was normally fine, but whenever we needed to generate end-of-month reports, CPU would spike and disk I/O would go red on Grafana. Customers complained about slow page loads, and we even started seeing timeouts.

Running SHOW PROCESSLIST revealed dozens of queries stuck in the Creating sort index or Copying to tmp table on disk state. That’s when I realized the problem: MySQL was creating temporary tables and writing them to disk instead of keeping them in RAM.

I once dealt with a database corruption incident at 3 AM and had to restore from backup — ever since, I’ve made a habit of checking backups daily. But performance issues with temporary tables are different: they don’t crash the server outright; they just gradually slow things down until users can’t tolerate it anymore.

Why MySQL Uses Temporary Tables — and When They Spill to Disk

MySQL automatically creates internal temporary tables for many types of queries: GROUP BY, ORDER BY on unindexed columns, DISTINCT, subqueries in the FROM clause, UNION, and various other scenarios.

Initially, MySQL tries to keep temporary tables in RAM using the MEMORY engine. But when the size exceeds the configured limit, MySQL converts them to MyISAM (or InnoDB starting from MySQL 8.0) and writes them to disk. That’s when performance tanks, because disk I/O is hundreds of times slower than RAM.

Two variables control this limit:

  • tmp_table_size — maximum size of an internal temporary table in RAM
  • max_heap_table_size — maximum size limit for MEMORY engine tables

MySQL uses the smaller of the two values as the effective limit. By default, both are typically 16MB — far too low for complex queries on large datasets. Check yours right away:

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

Diagnosing Whether Temporary Tables Are Causing Problems

Before changing anything, confirm that this is actually the root cause. Use SHOW STATUS to view server-wide statistics:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

The output looks like this:

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1847  |
| Created_tmp_files       | 23    |
| Created_tmp_tables      | 9341  |
+-------------------------+-------+

Calculate the ratio: Created_tmp_disk_tables / Created_tmp_tables. If this ratio exceeds 10–15%, you have a problem. The example above is 1847/9341 ≈ 19.7% — quite high. Reset the counters to measure over a specific time window:

FLUSH STATUS;
-- Wait a few hours, then measure again
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

For individual queries, use EXPLAIN to check whether a temporary table is being created:

EXPLAIN SELECT category, COUNT(*), AVG(price)
FROM orders
GROUP BY category
ORDER BY AVG(price) DESC;

If the Extra column in the results shows Using temporary; Using filesort, that query is creating a temporary table and sorting data on disk.

Solutions — From Quick Wins to Deeper Fixes

1. Increase the In-Memory Temporary Table Limit

The quickest fix. Edit /etc/mysql/my.cnf:

[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M

Or apply immediately without a restart (takes effect for new sessions only):

SET GLOBAL tmp_table_size = 268435456;
SET GLOBAL max_heap_table_size = 268435456;

Important caveat: this value multiplies by the number of concurrent connections. If you have 100 connections each using a 256MB temp table, theoretical RAM consumption can reach 25GB. I typically set 128–256MB for a 16GB RAM server with 50–100 concurrent connections. A useful reference formula:

tmp_table_size ≤ (RAM × 0.25) ÷ max_connections

2. Rewrite Queries to Avoid Generating Temporary Tables

This is the more sustainable solution. In many cases, temporary tables appear because queries aren’t optimized.

Avoid SELECT * with GROUP BY:

-- Bad: pulls all columns into the temp table before grouping
SELECT * FROM orders GROUP BY category;

-- Good: only select the columns you actually need
SELECT category, COUNT(*), SUM(amount)
FROM orders
GROUP BY category;

Replace subqueries in FROM with CTEs (MySQL 8.0+):

-- Old way: derived table = hidden temporary table
SELECT u.name, t.total
FROM users u
JOIN (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
) t ON u.id = t.user_id;

-- Better with CTE — the MySQL 8.0 optimizer handles this more efficiently
WITH order_totals AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  GROUP BY user_id
)
SELECT u.name, ot.total
FROM users u
JOIN order_totals ot ON u.id = ot.user_id;

3. Add Indexes in the Right Places So MySQL Can Skip Temporary Tables

MySQL most often needs to create a temporary table when there’s no suitable index for ORDER BY or GROUP BY. Adding a composite index usually solves this:

-- Query that frequently creates temp tables
SELECT category, status, COUNT(*)
FROM orders
WHERE created_at > '2024-01-01'
GROUP BY category, status;

-- Add a composite index covering both WHERE and GROUP BY columns
ALTER TABLE orders
ADD INDEX idx_date_cat_status (created_at, category, status);

After adding the index, re-run EXPLAIN and check whether Using temporary still appears in the Extra column.

4. Use the TempTable Engine (MySQL 8.0+)

MySQL 8.0 introduced the TempTable engine as a replacement for the old MEMORY engine. TempTable handles VARCHAR, TEXT, and BLOB types more efficiently — types that the MEMORY engine doesn’t support well and that tend to get forced to disk earlier.

-- Check which engine is currently in use
SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine';

-- Ensure TempTable is used (default in MySQL 8.0)
SET GLOBAL internal_tmp_mem_storage_engine = 'TempTable';

-- Limit the RAM pool for TempTable (default 1GB)
SET GLOBAL temptable_max_ram = 1073741824;

The Best Approach: Measure, Optimize, Then Measure Again

In practice, there’s no single solution that works for every situation. Here’s the process I follow:

  1. Establish a baseline: Run FLUSH STATUS, let the server run for 24 hours, then measure Created_tmp_disk_tables / Created_tmp_tables again
  2. Identify the offending queries: Enable the slow query log with log_queries_not_using_indexes = ON in my.cnf to find which queries create temporary tables most often
  3. Optimize queries first: Check whether you can add indexes or rewrite queries — this step typically delivers the highest ROI
  4. Tune configuration: Only after optimizing your queries should you raise tmp_table_size to handle the unavoidable cases
  5. Monitor the results: Measure again after a few days and compare the disk temp table ratio before and after

After that optimization, the Created_tmp_disk_tables / Created_tmp_tables ratio on our system dropped from nearly 20% to under 3%. The end-of-month report query went from 30 seconds down to 2–3 seconds. Disk I/O on Grafana returned to normal that same day.

One thing to keep in mind: temporary tables spilling to disk aren’t always a disaster. With truly large datasets, it’s sometimes unavoidable. The goal is to reduce their frequency and size to an acceptable level — not to eliminate them entirely.

Share:
Tags: