? MySQL Temporary Tables

? Quick Overview

Temporary tables in MySQL are short-lived tables that exist only for the duration of a database session (or until explicitly dropped). They are useful for storing intermediate results, simplifying complex queries, and improving readability of multi-step data processing.

? Key Concepts

Session-scoped: TEMPORARY tables persist only within the session that created them.
Visibility: Only the connection that creates the temporary table can see it.
Automatic cleanup: Temporary tables are dropped automatically when the session ends, or can be dropped manually with DROP TEMPORARY TABLE.
Same name as permanent table: You may create a temporary table with the same name as an existing permanent table; the temporary one hides the permanent within the session.

? Syntax / Theory

Basic creation syntax:

CREATE TEMPORARY TABLE table_name (column_definitions);

Common patterns:

CREATE ... AS SELECT to materialize a query result.
Indexes: You can define indexes on temporary tables just like normal tables.
Engine: Defaults to the server's default storage engine; InnoDB is common.

? Code Example(s)

? View Code Example

-- Create a temporary table to store recent orders
CREATE TEMPORARY TABLE recent_orders (
id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(10,2),
order_date DATETIME
);

? View Code Example

-- Insert data into the temporary table from a larger orders table
INSERT INTO recent_orders (id, customer_id, amount, order_date)
SELECT id, customer_id, total, created_at
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

? View Code Example

-- Use the temporary table to run an aggregated report
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
FROM recent_orders
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;

? View Code Example

-- Drop the temporary table explicitly (optional)
DROP TEMPORARY TABLE IF EXISTS recent_orders;

? Live Output / Explanation

What happens when you run the above:

The CREATE TEMPORARY TABLE statement creates an in-memory/onsite temporary structure accessible only to your session.
The INSERT ... SELECT copies recent rows into the temporary table quickly and isolates work from the main tables.
The aggregation query reads from recent_orders and returns the top customers for the last 30 days.
When your connection closes (or you execute DROP TEMPORARY TABLE), the temporary table is removed automatically.

? Use Cases

Staging intermediate results for complex ETL jobs.
Simplifying complicated joins by materializing subqueries.
Temporary storage during data migrations or batch processing.

? Tips & Best Practices

Use temporary tables to break complex queries into simpler steps for readability and debuggability.
Consider indexing columns used in JOINs or WHERE clauses on temporary tables for performance when they hold many rows.
Avoid creating very large temporary tables in memory—monitor tmp_table_size and max_heap_table_size settings.
If multiple sessions need shared intermediate data, use regular tables or a dedicated staging table—temporary tables are session-local.

? Try It Yourself / Practice Tasks

Create a temporary table that stores top-selling products for the last 7 days and query the top 5.
Test what happens if you open two client sessions: create a temporary table with the same name in both and verify visibility.
Measure query execution time with and without using a temporary table for a multi-join report (compare EXPLAIN outputs).