MySQL Temporary Tables

What a temporary table is

A MySQL temporary table is created for the current session and is automatically dropped when that session ends. It can hold intermediate results across several statements without leaving a permanent table in the schema. Temporary tables are useful for multi-step analysis, staging rows for a transformation, or reusing an intermediate result during one controlled database session. They are not automatically faster than a derived table or CTE; creation, population, indexing, and storage have costs.

Temporary table names are visible within the session and can shadow a permanent table with the same name for that session. This can cause confusing queries if a developer forgets that a temporary object exists. Choose distinct, descriptive names and explicitly drop temporary tables when their work is complete, even though the server will clean them up when the session closes.

Create and populate a staging table

CREATE TEMPORARY TABLE customer_totals (
  customer_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  order_count INT UNSIGNED NOT NULL,
  total_spend DECIMAL(12, 2) NOT NULL
);

INSERT INTO customer_totals (customer_id, order_count, total_spend)
SELECT customer_id, COUNT(*), SUM(total)
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

SELECT customer_id, order_count, total_spend
FROM customer_totals
WHERE total_spend > 1000;

This example stages a summary that can be queried by additional statements in the same session. A primary key provides a uniqueness guarantee and an access path for the later lookup. If the result is only needed once, a CTE or derived table may be simpler and avoid session state. If it is needed by multiple statements, consider whether the extra lifecycle and resource usage are justified.

Connection pooling changes the lifecycle

Temporary tables live as long as their database connection, not as long as an HTTP request or application function. A connection pool reuses sessions, so a temporary table can survive after one logical request finishes and be present for a later request that borrows the same connection. This can leak stale data, create name collisions, or expose one request's intermediate data to another. Ensure cleanup happens before returning the connection, or isolate operations on a dedicated session with a clear lifecycle.

The same caution applies to session variables, transaction state, and other connection-local settings. Always commit or roll back the transaction and release a borrowed connection in a finally-style cleanup path. Verify how the selected driver resets sessions when returning connections to a pool; do not assume all pool implementations reset every server-side object.

Indexes, storage, and transaction behavior

Temporary tables can use indexes, but index design still has a build and storage cost. Add a key or secondary index only when later operations benefit from it. Depending on definition, data size, MySQL version, and server settings, temporary data may use memory or spill to disk. Large staging results can consume significant resources and affect other sessions. Estimate cardinality and test with realistic data rather than assuming that a table called temporary is free.

Temporary table DDL and transaction behavior have MySQL-specific details. Do not assume CREATE TEMPORARY TABLE has the same implicit-commit behavior as ordinary CREATE TABLE; still verify the exact statement and version if transaction boundaries are important. Avoid placing destructive cleanup or bulk staging steps in production without checking the session, schema, and expected row counts.

Temporary table or another query tool?

Use a CTE when a named intermediate result improves one statement. Use a derived table for a small inline stage. Use a temporary table when multiple statements need the same staged data, when you need to create indexes on that intermediate result, or when the processing workflow is clearer as explicit steps. A permanent summary table is different: it persists across sessions and needs a refresh and consistency strategy. Compare these choices by lifetime, data volume, reuse, and operational complexity.

Temporary tables are session-local but still contain real data while they exist. Apply permissions, avoid sensitive data that is not needed, and prevent it from appearing in logs or dumps unexpectedly. Use parameterized statements for user inputs. If a staging operation transforms customer data, define who can access that connection and how it is cleaned up.

A safe operational checklist

Use a distinct name, create the expected columns and keys explicitly, populate a bounded and validated set, and verify row counts. Clean up after use and roll back failed work where appropriate. In pooled applications, test repeated calls on the same connection to expose stale temporary objects. Monitor memory, temporary disk usage, and query duration for large jobs. These checks turn temporary tables into a controlled tool instead of hidden persistent session state.

Temporary tables can make multi-step SQL understandable, but their scope is the database session. Choose them when the workflow benefits from session-local staging and manage connection reuse carefully. Continue with CTEs, connection pooling, and transactions.