MySQL Query Latency with Temporary Tables

, ,

When working with MySQL, you might notice something surprising: a query that runs quickly on its own suddenly becomes slow when you try to save the results into a temporary table.

For example, let’s say you run a SELECT query that completes in about two minutes. But when you try to capture those results with:

CREATE TEMPORARY TABLE table_name

SELECT field1, field2, field3;

the process hangs, forcing you to kill it after five minutes. What’s going on here?

Why It Happens

When you run CREATE TEMPORARY TABLE … SELECT, MySQL is actually doing two jobs:

  1. Running the SELECT – fetching rows from the source tables, applying filters, joins, and any transformations.
  2. Creating and populating the temporary table – defining a new schema on the fly and writing the rows into it.

That second step adds overhead. Here are the most common reasons for the slowdown:

  • Data Volume: Copying millions of rows into a new table takes time, even if the query itself executes quickly.
  • Indexes: Your source query might benefit from indexes, but inserts into the temporary table don’t—MySQL has to write all rows one by one.
  • Storage Engine: By default, temporary tables may spill to disk if they exceed memory limits, which is much slower.
  • Server Resources: CPU, memory, and I/O pressure can cause inserts into the temp table to crawl.
  • Locking/Concurrency: Other queries on the same data can introduce waits and contention.

Creating a Temporary Table in Memory

If your dataset isn’t too large, you can speed things up by creating the temporary table in memory instead of on disk. MySQL supports this via the MEMORY storage engine:

CREATE TEMPORARY TABLE temp_in_memory (

id INT,

name VARCHAR(255)

) ENGINE=MEMORY;

Then, you can insert data directly into this in-memory structure:

INSERT INTO temp_in_memory

SELECT field1, field2

FROM source_table;

Because the table lives in RAM, inserts and reads are much faster—though the tradeoff is size limits (governed by the max_heap_table_size and tmp_table_size settings).

Best Practices

If you’re seeing big slowdowns when creating temporary tables, try:

  • Reviewing and optimizing your SELECT query with proper indexing.
  • Using ENGINE=MEMORY if your temp dataset fits in RAM.
  • Monitoring resource usage during the query (SHOW PROCESSLIST, or performance schema tables).
  • Splitting large inserts into smaller batches.
  • Making sure the temp table schema only includes the columns and datatypes you really need.

Takeaway

A SELECT query that runs fast doesn’t always translate to a fast CREATE TEMPORARY TABLE. The extra overhead of creating and writing the table can introduce new bottlenecks. If you only need the results for lightweight, session-specific work, an in-memory temporary table (ENGINE=MEMORY) can be a game-changer.

By understanding how MySQL handles temporary tables under the hood, you can choose the right approach and keep your queries moving fast.