Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 75 ยท Tables, Constraints and Schema Design

Temporary Tables and Derived Data

Understand MySQL temporary tables: scope, transactions, the reopen limit, CTE alternatives and internal temp tables controlled by tmp_table_size.

MySQL uses temporary tables in two very different ways, and both are worth understanding. The first kind you create yourself with CREATE TEMPORARY TABLE, to hold intermediate results for your own session. The second kind you never see: the server builds internal temporary tables on its own whenever a query needs scratch space, for example to group rows or to materialize a derived table.

The CREATE TABLE lesson introduced the syntax in a few lines. This lesson goes into how they behave: their scope, how they interact with transactions and replication, the "can't open it twice" limit, when a CTE or derived table is the better choice, and how to notice and tune the internal ones. MySQL-specific statements are read-only here; the runnable examples use SQLite temporary tables to practice the patterns.

Your own temporary tables

SQL
CREATE TEMPORARY TABLE recent_orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  KEY (customer_id)
) ENGINE=InnoDB;

INSERT INTO recent_orders
SELECT id, customer_id, total FROM orders WHERE placed_at >= CURDATE() - INTERVAL 7 DAY;

A temporary table can have indexes, a primary key and a storage engine, and you can INSERT, UPDATE, DELETE and JOIN it like any table. Foreign keys are not supported on temporary tables, and neither is partitioning. You can also fill it in one step with CREATE TEMPORARY TABLE ... AS SELECT or ... LIKE, which the copying lesson covers.

The privilege needed is CREATE TEMPORARY TABLES on the database. It is a separate grant, so an application account can be allowed scratch tables without being allowed to create permanent ones.

Scope and lifetime

A temporary table belongs to one connection:

  • Other sessions can't see it, and they can create their own table with the same name.
  • It is dropped automatically when the connection ends.
  • It shadows a permanent table of the same name for that session, and SHOW TABLES doesn't list it. Its definition is not in information_schema.TABLES; MySQL 8.0 does expose them in information_schema.INNODB_TEMP_TABLE_INFO for InnoDB ones.

In a connection pool, "the connection ends" may be hours away. A temp table created by a request survives on the pooled connection, and the next request that gets that connection finds it still there with old rows. Always clean up explicitly:

SQL
DROP TEMPORARY TABLE IF EXISTS recent_orders;

DROP TEMPORARY TABLE removes only a temporary table. Plain DROP TABLE drops the temporary table if one exists and otherwise the permanent table of that name, so if your cleanup code runs twice, the second run can destroy real data.

Session-scoped patterns, live

SQLite temp tables behave the same way, so this example practices the common pattern: compute an expensive aggregate once, index it, reuse it, drop it.

SQL Compute once, reuse several times

Press Run (or Ctrl + Enter) to see the result here.

The last query returns no rows because the table is gone, and its index went with it. In MySQL you would write DROP TEMPORARY TABLE.

Transactions, DDL and replication

Temporary tables have a few special rules in MySQL:

  • CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE do not commit the current transaction, unlike other DDL statements. You can create one in the middle of a transaction without ending it.
  • An InnoDB temporary table takes part in the transaction as usual: ROLLBACK undoes its row changes. A MyISAM temporary table does not.
  • The reopen limit. You cannot refer to the same temporary table more than once in a single statement. This fails with error 1137, "Can't reopen table":
SQL
SELECT a.id, b.id
FROM recent_orders AS a
JOIN recent_orders AS b ON a.customer_id = b.customer_id AND a.id < b.id;   -- error 1137

The fixes are to copy the table into a second temporary table, to build the intermediate result as a CTE (a CTE can be referenced many times), or to rewrite the query with a window function.

  • Replication. With the default binlog_format=ROW, temporary tables are not written to the binary log at all, only the row changes to permanent tables they produce. With statement-based logging, temporary tables are logged and replayed on replicas, which makes failover risky because a replica that restarts loses them. Prefer ROW format, as the replication lesson recommends.
  • Temporary tables created by CREATE ... SELECT follow the same rules.

Alternatives: derived tables and CTEs

A temporary table is a stored intermediate result. Often you don't need storage at all:

Tool Stored? Reusable in one statement Indexable Lifetime
Derived table (FROM (SELECT ...) AS d) Materialized or merged by the optimizer No, written once per use No One statement
CTE (WITH x AS (...)) Same as derived Yes, multiple references No One statement
Temporary table Yes Yes (once, see limit above) Yes Session
Permanent table Yes Yes Yes Until dropped

Use a derived table or CTE when one statement is enough: the optimizer can merge it into the outer query or push conditions into it, and nothing needs cleanup. Use a temporary table when the same result feeds many statements, when you need to index the intermediate data, or when you want to break a monstrous query into steps whose results you can inspect.

Internal temporary tables

Separate from the above, MySQL creates internal temporary tables automatically. You can spot them in EXPLAIN's Extra column as Using temporary, and the usual causes are:

  • GROUP BY or DISTINCT on columns no index can deliver in order,
  • ORDER BY and GROUP BY on different columns,
  • UNION (not UNION ALL), which must remove duplicates,
  • derived tables and views that the optimizer cannot merge (the TEMPTABLE algorithm),
  • some subqueries and window functions that need to buffer rows.

An internal temporary table starts in memory. In MySQL 8.0 and later, the engine for that is TempTable (internal_tmp_mem_storage_engine, default TempTable). It is limited by temptable_max_ram, which defaults to a few percent of server memory (between 1 and 4 GB since 8.0.28). When the limit is reached, or when a column type can't live in memory (very large TEXT or BLOB), the table is moved to disk as an InnoDB temporary table in the session temporary tablespace.

The older settings still matter in some cases: tmp_table_size and max_heap_table_size cap the in-memory MEMORY engine tables (both 16 MB by default, the smaller wins), and they apply when you choose internal_tmp_mem_storage_engine=MEMORY.

Noticing and tuning

Measure how often tables spill to disk:

SQL
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables        1,204,311
-- Created_tmp_disk_tables      88,201
-- Created_tmp_files               142

Here about 7% of internal temporary tables went to disk. The usual sequence of fixes is:

  1. Find the queries with sys.statements_with_temp_tables (or EXPLAIN looking for Using temporary).
  2. Add or change an index so that GROUP BY or DISTINCT can be read in index order and the temporary table disappears. The composite index lesson shows how.
  3. Select fewer columns. Wide rows with TEXT columns force disk tables early.
  4. Only then raise temptable_max_ram (or tmp_table_size), remembering the limit applies per table and per session, so concurrent queries multiply it.

Also make sure the directory in tmpdir is on fast storage with enough free space: large on-disk temporary tables and filesort files use it.

A quick checklist

  • Name your temp tables with a prefix like tmp_ so they can't be confused with permanent ones.
  • Drop them explicitly when pooled connections are in play.
  • Remember: no foreign keys, no second reference in one statement, no listing in SHOW TABLES.
  • Prefer a CTE for a single statement and a temp table for reuse or indexing.
  • Treat Using temporary in EXPLAIN as a hint to look for an index, not as something to cover with memory.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Who can see a table created with CREATE TEMPORARY TABLE?

    A temporary table is private to its creating session and is dropped when that session ends.
  2. 2. What happens if you reference the same temporary table twice in one statement, as in a self-join?

    MySQL cannot reopen a temporary table inside one statement. Use a CTE or a second temporary table instead.
  3. 3. Does CREATE TEMPORARY TABLE commit the current transaction?

    CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE are exceptions to the implicit-commit rule for DDL.
  4. 4. EXPLAIN shows 'Using temporary'. What is the best first response?

    An index that delivers rows in the needed order can remove the temporary table entirely. Raising memory limits only hides the cost.
  5. 5. When is a CTE a better choice than a temporary table?

    A CTE needs no cleanup and lets the optimizer merge or push conditions into it. Temp tables win when many statements reuse an indexed result.

Summary

  • CREATE TEMPORARY TABLE makes a session-private table that is dropped at disconnect; it can have indexes but no foreign keys, and it needs the CREATE TEMPORARY TABLES privilege.
  • Drop temp tables explicitly with DROP TEMPORARY TABLE, especially on pooled connections.
  • It doesn't commit the current transaction, can't be referenced twice in one statement (error 1137) and is not binary-logged under row-based logging.
  • CTEs and derived tables replace temp tables for single-statement logic; temp tables win for reuse and indexing.
  • Internal temporary tables (Using temporary) are created by GROUP BY, DISTINCT, UNION and unmerged derived tables, live in memory with the TempTable engine and spill to disk when limits are hit.
  • Fix spills with indexes and narrower selects first, memory limits second.

Next: Keys and Constraints.