How Splitting a MySQL Query Eliminated Temp‑Table I/O and Boosted Speed 100×

Problem

Online MySQL database showed a slow query; DBA observed server I/O spiking to 100% and execution time around 7 seconds.

SQL statement:

SELECT DISTINCT g.*, cp.name AS cp_name, c.name AS category_name, t.name AS type_name FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20;

Analysis

Explain plan (see image) shows the query scans a large amount of data, including all columns (g.*), then creates a temporary table for ORDER BY, which exceeds memory and is written to disk, causing high I/O.

Optimization Plan

The overall idea is to split the SQL, separating the sorting from the data retrieval.

First statement (fetch IDs only):

SELECT DISTINCT g.id FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20;

Second statement (fetch full rows using the IDs):

SELECT DISTINCT g.*, cp.name AS cp_name, c.name AS category_name, t.name AS type_name FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 AND g.id IN ( … ) ORDER BY g.modify_time DESC;

Measured Results

On a SATA machine, original query took ~50 s; after optimization, the two queries took 0.3 s and 0.1 s respectively, achieving over 100× speedup and reducing I/O from 100% to <1%.

On an SSD machine, original query took ~7 s; after optimization, the times were 0.3 s and 0.1 s, a >10× speedup with I/O also dropping below 1%.

Thus, before optimization the disk I/O was the bottleneck; after optimization the bottleneck disappeared, and SSD and SATA performance became comparable.

Theoretical Analysis

MySQL may use temporary tables during query execution, which generally indicates lower performance.

Temporary Table Storage

MySQL has in‑memory temporary tables (MEMORY engine) and on‑disk temporary tables (MyISAM engine). When the in‑memory table exceeds the configured size, it is flushed to disk, typically under /tmp on Linux.

When Temporary Tables Are Used

1) ORDER BY or GROUP BY that requires sorting not covered by an index. 2) JOIN queries where ORDER BY/GROUP BY reference columns from a non‑first table. 3) ORDER BY combined with DISTINCT. 4) SELECT statements with SQL_SMALL_RESULT (usually unnecessary).

When Disk Temporary Tables Are Forced

1) Tables containing TEXT or BLOB columns. 2) GROUP BY or DISTINCT on columns larger than 512 bytes. 3) UNION/UNION ALL with SELECT columns larger than 512 bytes.

Temporary Table Configuration

tmp_table_size – maximum size of internal memory temporary tables. max_heap_table_size – maximum size of user‑created memory tables. The effective memory temporary table size is the smaller of the two.

Table Design Principles

Avoid temporary tables because they degrade performance, especially disk‑based ones. Common avoidance methods:

1) Create indexes on columns used in ORDER BY or GROUP BY. 2) Split large TEXT/BLOB columns into separate tables if they are not needed for query conditions.

SQL Optimization

If redesign is difficult, optimize the SQL to reduce temporary table size:

1) Split queries so that sorting and detailed data retrieval are separate. 2) Remove unnecessary sorting or grouping when business logic permits.

How to Detect Temporary Table Usage

Run EXPLAIN and check the Extra column for “Using temporary”. See MySQL manual for details.