1. [Beginner’s Guide] What Is a MySQL Temporary Table? Differences From Regular Tables
Introduction
When managing data with MySQL, there are situations where you need to store data temporarily. For example, when processing large volumes of data, you might want to work while temporarily saving the data. In such cases, a “Temporary Table” is useful.
This article provides a detailed explanation of the basic mechanism of MySQL temporary tables and their differences from regular tables.
1-1. What Is a Temporary Table?
A temporary table (Temporary Table) is a special table that exists only for the duration of a database session (connection). Unlike regular tables, it is automatically dropped when the session ends, making it ideal for temporary data storage.
Key Features of Temporary Tables
Independent per session Temporary tables can be accessed only within the session that created them. They cannot be referenced from other sessions.
Automatically deleted when the session ends Temporary tables disappear automatically when the session ends, even without explicit deletion.
Can create temporary tables with the same name Unlike regular tables, you can create temporary tables with the same name in different sessions.
1-2. Differences From Regular Tables
There are the following differences between temporary tables and regular tables.
Comparison Item
Temporary Table
Regular Table
Data retention period
Valid only during the session (auto-deleted)
Persistently retained
Accessible scope
Only within the creating session
Accessible to all users
Table name conflicts
Can create temporary tables with the same name
Cannot create tables with the same name in the same database
Privileges
CREATE TEMPORARY TABLES privilege required
Usual CREATE TABLE privilege required
Indexing
Possible
Possible
Performance
Often created in memory, enabling fast processing
Stored on disk, and processing load increases with large data volumes
Which Should You Use?
When you need to store data temporarily and it becomes unnecessary after processing → Temporary Table
When you need to keep data permanently and reuse it → Regular Table
For example, tasks such as large-scale data analysis or temporary data aggregation can benefit greatly from temporary tables.
1-3. Cases Where a Temporary Table Is Needed
MySQL temporary tables are especially useful in the following scenarios.
① Improving Query Performance
For example, when performing complex JOIN operations, creating a temporary table in advance to store intermediate data can reduce processing time.
Example: Reducing JOIN Load
CREATE TEMPORARY TABLE temp_users AS
SELECT id, name FROM users WHERE status = 'active';
By storing the target data in a temporary table before executing the JOIN, performance improves.
② Temporary Data Storage
Temporary tables are also handy for managing temporary data in applications. For example, you can store user search results in a temporary table and delete them after the session ends.
③ Intermediate Tables for Batch Processing
When processing large datasets, using a temporary table as an intermediate table can improve processing stability.
1-4. Limitations of Temporary Tables
Temporary tables are convenient, but they come with several limitations.
① Deleted When Session Ends
Since temporary tables are automatically deleted when the session ends, they are unsuitable for persistent data storage.
② Not Accessible From Other Sessions
Because temporary tables are only usable within the session that created them, they cannot be shared with other users or processes.
③ Potential Conflict With Regular Tables of Same Name
If a regular table with the same name exists, creating a temporary table will cause the regular table to become temporarily invisible, so caution is required.
CREATE TEMPORARY TABLE users (id INT, name VARCHAR(255));
SELECT * FROM users; -- This query references the temporary table's data
Thus, when a temporary table is created, you cannot access the regular table with the same name, so be careful with naming.
Conclusion
MySQL temporary tables are a handy feature that helps with temporary data storage and query optimization. By understanding the differences from regular tables and using them appropriately, you can achieve more efficient data processing.
✔ Recap
Temporary tables are automatically deleted when the session ends
Unlike regular tables, they are independent per session
Ideal for temporary data storage and improving query performance
Since data disappears when the session ends, they are unsuitable for permanent data storage
Not accessible from other sessions; may conflict with regular tables of the same name
2. [With Sample Code] How to Create Temporary Tables in MySQL
Introduction
In the previous article, we explained the basic concepts of temporary tables (Temporary Table) and the differences from regular tables.
This time, we will provide a detailed explanation of how to actually create a temporary table and manipulate its data. Creating a temporary table is straightforward, but if you don’t use the proper syntax, it may not behave as expected. This article provides a detailed explanation of “basic syntax”, “creating from an existing table”, “methods to verify temporary tables”, among other topics.
2-1. Basic Syntax for Temporary Tables
To create a temporary table, use the CREATE TEMPORARY TABLE statement.
This confirms that data is stored in the temporary table.
2-5. Precautions When Creating Temporary Tables
① Beware of Table Name Conflicts
If you create a temporary table with the same name as a regular table, the temporary table takes precedence, making the regular table temporarily inaccessible.
CREATE TEMPORARY TABLE users (id INT, name VARCHAR(50));
SELECT * FROM users; -- This retrieves data from the temporary table
Therefore, it is recommended to prefix temporary table names with something like “temp_”.
② Indexes Are Not Inherited Automatically
When copying data from an existing table, indexes are not applied automatically.
Add indexes explicitly as needed.
ALTER TABLE temp_users ADD INDEX (email);
③ Permission Required to Create TEMPORARY TABLE
Creating a temporary table requires the CREATE TEMPORARY TABLES privilege.
GRANT CREATE TEMPORARY TABLES ON database_name.* TO 'user'@'localhost';
Without this privilege, you cannot create temporary tables.
Conclusion
This article explained how to create temporary tables.
✔ Recap
Create a temporary table using CREATE TEMPORARY TABLE
Can also create by copying data from an existing table
Automatically dropped when the session ends
Indexes are not applied automatically, so be careful
Recommend using a prefix like “temp_” to avoid table name conflicts
3. How to Manipulate Data in MySQL Temporary Tables (INSERT, UPDATE, DELETE)
Introduction
In the previous article, we explained how to create temporary tables in MySQL. This time, we will explain how to add, update, and delete data in temporary tables using concrete SQL commands. Temporary tables can be manipulated just like regular tables, but there are several caveats, which we will also discuss in detail.
3-1. Adding Data to Temporary Tables (INSERT)</3>
Adding data to a temporary table uses the INSERT INTO statement just like a regular table.
Basic Syntax
INSERT INTO temporary_table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Example: Adding User Information
CREATE TEMPORARY TABLE temp_users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO temp_users (name, email)
VALUES
('Taro Tanaka', 'tanaka@example.com'),
('Hanako Sato', 'sato@example.com');
Adding Existing Data Using INSERT…SELECT
You can also retrieve from an existing table and insert it into a temporary table.
INSERT INTO temp_users (id, name, email)
SELECT id, name, email FROM users WHERE status = 'active';
Using this method, you can store only active users in the temporary table.
3-2. Updating Data in Temporary Tables (UPDATE)
To modify data in a temporary table, use the UPDATE statement.
Basic Syntax
UPDATE temporary_table_name
SET column_name = value
WHERE condition;
Example: Updating a User’s Name
UPDATE temp_users
SET name = 'Ichiro Tanaka'
WHERE email = 'tanaka@example.com';
Bulk Updating Data with Specific Conditions
For example, if you want to change email addresses of a specific domain to example.jp, you can write as follows.
UPDATE temp_users
SET email = REPLACE(email, 'example.com', 'example.jp')
WHERE email LIKE '%@example.com';
3-3. Deleting Data from Temporary Tables (DELETE)
To delete data, use the DELETE statement.
Basic Syntax
DELETE FROM temporary_table_name WHERE condition;
Example: Deleting Specific User Data
DELETE FROM temp_users WHERE email = 'tanaka@example.com';
Deleting All Data (Difference from TRUNCATE)
If you want to delete all data, you can write as follows.
DELETE FROM temp_users;
On the other hand, for regular tables you can use TRUNCATE TABLE to delete data quickly, but TRUNCATE cannot be used on temporary tables.
TRUNCATE TABLE temp_users; -- Error (TRUNCATE cannot be used on temporary tables in MySQL)
Therefore, you need to use DELETE to remove all data from a temporary table.
3-4. Considerations When Manipulating Temporary Table Data
① Data Disappears When the Session Ends
Temporary tables are automatically dropped when the session (connection) ends, so they are not suitable for persisting data.
② Not Visible to Other Sessions
Temporary tables are only visible within the session that created them and cannot be accessed from other sessions.
If you run this SQL in another session, you will get an error like “Table ‘temp_users’ doesn’t exist”.
③ Indexes Are Not Automatically Applied to Temporary Tables
When you create a table with CREATE TEMPORARY TABLE ... AS SELECT ..., the indexes from the original table are not inherited. If needed, use ALTER TABLE to add indexes manually.
ALTER TABLE temp_users ADD INDEX (email);
Conclusion
In this article, we covered data manipulation (INSERT, UPDATE, DELETE) for temporary tables.
✔ Review
INSERT to add data (INSERT INTO ... VALUES / INSERT INTO ... SELECT)
UPDATE to modify data (updates with specific conditions and using REPLACE())
DELETE to remove data (DELETE FROM ... WHERE, TRUNCATE not allowed)
Temporary tables are dropped when the session ends
Not accessible from other sessions
Indexes are not applied automatically, so they must be added manually
4. Are MySQL Temporary Tables Automatically Deleted? Also Explaining How to Delete Them Manually
Introduction
MySQL temporary tables (Temporary Table) differ from regular tables in that they are automatically deleted when the session ends. However, there are cases where you need to delete them manually. In this article, we will explain in detail the mechanism of automatic deletion of temporary tables and the methods for deleting them manually.
4-1. How Automatic Deletion of Temporary Tables Works
① Automatically Deleted When the Session Ends
MySQL temporary tables are automatically deleted when the session (the database connection) that created the table ends. Therefore, you generally don’t need to delete them manually.
Example: Automatic Deletion When the Session Ends
-- Create a temporary table in a new session
CREATE TEMPORARY TABLE temp_users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
email VARCHAR(100)
);
-- Insert data
INSERT INTO temp_users (name, email) VALUES ('Taro Tanaka', 'tanaka@example.com');
the session (close the MySQL client connection)
EXIT;
At this point, the temporary table temp_users is automatically deleted.
② However, the Temporary Table Remains as Long as the Session Continues
Because temporary tables are managed per session, they are not deleted as long as the session continues.
SELECT * FROM temp_users; -- Data can be retrieved as long as the session continues
In other words, the temporary table stays in memory until you close the MySQL client (or program).
4-2. How to Delete Temporary Tables Manually
Temporary tables can also be deleted manually. In MySQL, you can delete a temporary table using DROP TEMPORARY TABLE.
① Use DROP TEMPORARY TABLE
DROP TEMPORARY TABLE temp_users;
This immediately deletes the temporary table temp_users.
② Add IF EXISTS to Prevent Errors
If the table to be deleted does not exist, you can use IF EXISTS to prevent an error.
DROP TEMPORARY TABLE IF EXISTS temp_users;
Using this syntax avoids an error even when the table does not exist.
③ Different from Regular DROP TABLE
If you try to delete a temporary table with the regular DROP TABLE, you may encounter the following error.
DROP TABLE temp_users;
Error:
ERROR 1051 (42S02): Unknown table 'temp_users'
MySQL manages regular tables and temporary tables separately, so you must always use DROP TEMPORARY TABLE to delete a temporary table.
4-3. How to Verify Deletion of Temporary Tables
① SHOW TABLES Does Not List Them
The regular SHOW TABLES command does not display temporary tables.
SHOW TABLES;
→ Temporary tables are not shown in the list
② Check with INFORMATION_SCHEMA
You can use MySQL’s INFORMATION_SCHEMA to check whether a temporary table exists.
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'temp_users';
If this query returns a result, it means the temporary table exists.
4-4. Precautions When Deleting Temporary Tables
Different Sessions Have Their Own Temporary Tables
You can create temporary tables with the same name in multiple sessions. You cannot delete a temporary table created by another session.
Example
-- Created in session A
CREATE TEMPORARY TABLE temp_data (id INT);
-- Attempt to delete in session B
DROP TEMPORARY TABLE temp_data;
Error:
ERROR 1051 (42S02): Unknown table 'temp_data'
Temporary tables can only be deleted by the session that created them.
② Potential Conflict with Regular Tables of the Same Name
If a temporary table shares a name with a regular table, the temporary table takes precedence and the regular table becomes invisible.
Example
-- Regular table (users) exists
SELECT * FROM users;
-- Create a temporary table with the same name
CREATE TEMPORARY TABLE users (id INT, name VARCHAR(50));
-- Executing here will reference the temporary table users
SELECT * FROM users;
Solution:
Give temporary tables a prefix such as temp_ to avoid name collisions.
Summary
In this article, we explained the mechanisms and methods for deleting temporary tables.
✔ Recap
Temporary tables are automatically deleted when the session ends
As long as the session continues, the temporary table remains
When deleting manually, use DROP TEMPORARY TABLE
Adding IF EXISTS prevents errors
SHOW TABLES does not display temporary tables
Only the session that created the table can delete it
If a name conflicts with a regular table, the regular table becomes invisible, so use a prefix for the name
5. Five Use Cases for MySQL Temporary Tables! Also Useful for Boosting Performance
Introduction
MySQL temporary tables (Temporary Table) allow you to store temporary data while simplifying complex queries, improving database performance. In this article, we introduce five concrete use cases for temporary tables. We’ll explain how they can be useful in real-world work, along with sample SQL.
When processing large datasets, executing a JOIN between tables directly can degrade performance.
Solution
By using a temporary table to pre-filter the target data, you can reduce the load of the JOIN.
Example: Retrieve Order Data for Active Users
-- First, store only active users in a temporary table
CREATE TEMPORARY TABLE temp_active_users AS
SELECT id, name FROM users WHERE status = 'active';
-- Execute JOIN using the temporary table
SELECT o.order_id, t.name, o.total_price
FROM orders o
JOIN temp_active_users t ON o.customer_id = t.id;
Benefits
By targeting only “active users” instead of the entire users table, the JOIN load is reduced
Simplifying the main query improves readability
5-2. Aggregating Temporary Data
Problem
Running the same aggregation multiple times degrades performance.
Solution
By using a temporary table to store the aggregation result once, you can prevent unnecessary repeated calculations.
Example: Store Monthly Sales Data in a Temporary Table
-- Calculate monthly sales totals and store them in a temporary table
CREATE TEMPORARY TABLE temp_monthly_sales AS
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total_price) AS total_sales
FROM orders
GROUP BY month;
-- Retrieve aggregation results using the temporary table
SELECT * FROM temp_monthly_sales WHERE total_sales > 100000;
Benefits
Aggregated data saved in the temporary table can be reused any number of times
5-3. Storing Intermediate Data in Batch Processing
Problem
When performing bulk updates or deletions, an error occurring mid-process can leave data in an incomplete state.
Solution
Use a temporary table to store intermediate data, ensuring data consistency.
Example: Update Order Data Based on Specific Conditions
-- Store data to be updated in a temporary table
CREATE TEMPORARY TABLE temp_orders AS
SELECT order_id, total_price FROM orders WHERE status = 'pending';
-- Perform update using the temporary table
UPDATE orders o
JOIN temp_orders t ON o.order_id = t.order_id
SET o.total_price = t.total_price * 1.1; -- 10% price increase
Benefits
Store target data in a temporary table for safe updates
Post-update data verification becomes easy
5-4. Managing Temporary Data per User
Problem
When you need to manage temporary user data per session, regular tables tend to accumulate unnecessary data.
Solution
Using temporary tables automatically deletes data when the session ends, eliminating maintenance overhead.
Example: Store Search Criteria in a Temporary Table
-- Store each user's search results in a temporary table
CREATE TEMPORARY TABLE temp_search_results AS
SELECT * FROM products WHERE category = 'electronics';
-- Display search results
SELECT * FROM temp_search_results;
Benefits
Data is automatically deleted when the session ends
Temporary search results are saved and can be reused
5-5. Choosing Between Temporary Tables and Views
Problem
We want to optimize performance of frequently run queries, but when you need to retain temporary data, it’s unclear whether to use a view (VIEW) or a temporary table.
Solution
When data does not change → Use a view (VIEW)
When data changes frequently → Use a temporary table
Example: Case Using a Temporary Table
CREATE TEMPORARY TABLE temp_high_value_customers AS
SELECT customer_id, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 50000;
SELECT * FROM temp_high_value_customers;
Example: Case Using a View
CREATE VIEW high_value_customers AS
SELECT customer_id, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 50000;
Benefits
Temporary tables store data, providing performance improvement
Views are convenient for query reuse, but with large data volumes they may degrade performance
Conclusion
In this article, we presented five use cases for MySQL temporary tables.
✔ Recap
Optimizing query performance (reducing JOIN load) → Save only the necessary data in a temporary table before performing the JOIN
Aggregating temporary data → Store aggregation results in a temporary table to avoid repeating the same calculation
Storing intermediate data in batch processing → Use a temporary table for safe processing during large data updates
Managing temporary data per user → Temporary tables automatically delete when the session ends, preventing leftover data
Choosing between temporary tables and views → Use a temporary table when data changes, use a view when it does not
6. Three precautions for using MySQL temporary tables safely
Introduction
MySQL temporary tables (Temporary Table) are a convenient feature that operate independently per session and are automatically removed under certain conditions. However, if used incorrectly, they can lead to degraded database performance or unexpected errors. This article explains the three precautions you should keep in mind to use temporary tables safely.
6-1. Precaution 1: Don’t rely too heavily on automatic deletion at session end
Issue
MySQL temporary tables are automatically deleted when the session ends. Therefore, it may seem that explicit deletion is unnecessary, but in some cases it can cause unintended trouble.
Examples of trouble
If the connection remains open for a long time, it continues to consume memory
When a session stays open, temporary tables are not deleted and continue to consume database resources.
If not explicitly deleted, it can cause design mistakes in the program
If an unexpected reconnection occurs during batch processing, the temporary table disappears and causes an error.
Solution
Explicitly delete unnecessary temporary tables with DROP TEMPORARY TABLE
In cases where connections last long, such as batch processing, delete temporary tables periodically
Example: Explicitly delete a temporary table
DROP TEMPORARY TABLE IF EXISTS temp_users;
Key point
Adding IF EXISTS prevents errors even when the table does not exist.
6-2. Precaution 2: Potential name conflicts with regular tables
Issue
Temporary tables can be created with the same name as regular tables. However, when a temporary table is created, the regular table with the same name becomes temporarily invisible.
Example of trouble
-- A regular users table exists
SELECT * FROM users;
-- Create a temporary table with the same name
CREATE TEMPORARY TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
-- Executing here retrieves data from the temporary table instead of the regular table
SELECT * FROM users;
Thus, while a temporary table exists, the regular table with the same name becomes invisible, which can lead to unexpected data retrieval errors.
Solution
Add a prefix such as “temp_” to temporary table names
Adopt a naming convention that makes it easy to distinguish regular tables from temporary tables
Example: Creating a safe temporary table
CREATE TEMPORARY TABLE temp_users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
Benefits
Adding temp_ prevents conflicts with the regular users table.
Makes it easier to clearly distinguish them in code.
6-3. Precaution 3: Indexes and constraints are not automatically inherited
Issue
When a table is created with CREATE TEMPORARY TABLE ... AS SELECT ..., the original table’s indexes and constraints are not inherited, which can degrade performance.
Example of trouble
-- Regular users table (with indexes)
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100) UNIQUE,
name VARCHAR(50)
);
-- Create a temporary table (indexes are not inherited at this time)
CREATE TEMPORARY TABLE temp_users AS
SELECT id, email, name FROM users;
In this case, temp_users does not inherit PRIMARY KEY or UNIQUE constraints, leading to slower query performance and a higher likelihood of duplicate data.
Solution
Explicitly add indexes after creating the temporary table
If defining columns directly with CREATE TEMPORARY TABLE, specify indexes beforehand
Example: Manually adding indexes
CREATE TEMPORARY TABLE temp_users (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100) UNIQUE,
name VARCHAR(50)
);
ALTER TABLE temp_users ADD INDEX idx_email (email);
Using this method, you can create a temporary table that has the same indexes as the original table.
Summary
In this article, we explained three precautions for using temporary tables safely.
✔ Review
Don’t rely too heavily on automatic deletion at session end
Explicitly delete unnecessary temporary tables with DROP TEMPORARY TABLE
If connections last long, delete them periodically
Potential name conflicts with regular tables
If a regular table with the same name exists, the temporary table takes precedence
Use a prefix like temp_ to clearly differentiate
Indexes and constraints are not automatically inherited
With CREATE TEMPORARY TABLE ... AS SELECT ..., indexes disappear
Add indexes manually after creation
By keeping these points in mind, you can safely leverage MySQL temporary tables while improving database performance!
7. 10 Frequently Asked Questions About MySQL Temporary Tables (FAQ)
Introduction
MySQL temporary tables (Temporary Table) are explained in a 10-FAQ format covering common questions encountered in real-world usage. From how they work and their limitations to performance issues and troubleshooting, we will answer a wide range of questions.
7-1. Questions About Basic Specifications
Q1. Can temporary tables be referenced from other sessions?
A. No, they cannot be referenced. Temporary tables are valid only within the session that created them and cannot be accessed from other sessions.
-- Created in session A
CREATE TEMPORARY TABLE temp_users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
-- Attempt to access from session B (will error)
SELECT * FROM temp_users;
As a solution, if you want to share data with other sessions, you need to use a regular table.
Q2. Are temporary tables stored on disk?
A. Generally they are stored in memory, but they may be moved to disk under certain conditions. If MySQL’s tmp_table_size or max_heap_table_size is exceeded, temporary tables may be created on disk in InnoDB or MyISAM format.
SHOW VARIABLES LIKE 'tmp_table_size';
To improve performance, it is important to set tmp_table_size appropriately.
Q3. Can indexes be set on temporary tables?
A. Yes, you can. You can set PRIMARY KEY and INDEX just like on regular tables.
CREATE TEMPORARY TABLE temp_users (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100) UNIQUE,
name VARCHAR(50)
);
ALTER TABLE temp_users ADD INDEX idx_email (email);
However, when using CREATE TEMPORARY TABLE ... AS SELECT ..., indexes are not inherited, so you need to add them manually.
7-2. Performance and Behavior Questions
Q4. Are there any specification changes for temporary tables in MySQL 8.0?
A. MySQL 8.0 introduced Common Table Expressions (CTE) using the WITH clause. From MySQL 8.0 onward, you can process temporary data using the WITH clause without using temporary tables.
WITH temp_users AS (
SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM temp_users;
Using CTEs instead of temporary tables can simplify queries and save memory.
Q5. What is the difference from MySQL’s MEMORY tables?
A. The difference is that MEMORY tables retain data across sessions. Temporary tables are deleted when the session ends, whereas MEMORY tables keep data until the server is restarted.
CREATE TABLE memory_table (
id INT PRIMARY KEY,
name VARCHAR(50)
) ENGINE=MEMORY;
Guidelines for Choosing
Use temporary tables for short‑term data processing
Use MEMORY tables when high‑speed access is needed
7-3. Deletion and Troubleshooting Questions
Q6. Can you delete a temporary table with DROP TABLE?
A. No, you need to use DROP TEMPORARY TABLE. When deleting a temporary table, always use DROP TEMPORARY TABLE.
DROP TEMPORARY TABLE temp_users;
Using the regular DROP TABLE may result in an error.
Q7. Why doesn’t SHOW TABLES display temporary tables?
A. SHOW TABLES does not display temporary tables. To check temporary tables, use INFORMATION_SCHEMA.
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'temp_users';
If the table is not shown in INFORMATION_SCHEMA.TABLES, there is a possibility that it has already been deleted.
Summary
In this article, we introduced 10 frequently asked questions about MySQL temporary tables.
✔ Recap
Temporary tables cannot be referenced from other sessions
Created in memory, but move to disk when they become large
Indexes must be set manually
From MySQL 8.0 onward, you can use CTEs (WITH clause)
Unlike MEMORY tables, they disappear when the session ends
Must be deleted with DROP TEMPORARY TABLE
SHOW TABLES does not show temporary tables
8. Summary: MySQL Temporary Table Usage Tips
Introduction
MySQL temporary tables (Temporary Table) are a powerful tool for storing temporary data and optimizing query performance .
We will summarize the discussion so far and organize the key points for effectively using temporary tables.
8-1. Basic Concepts of MySQL Temporary Tables
What Is a Temporary Table?
A table that exists independently for each session
Automatically deleted when the session ends
Supports INSERT, UPDATE, DELETE just like regular tables
Basic Creation Method
CREATE TEMPORARY TABLE temp_users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
email VARCHAR(100)
);
Main Uses
Storing temporary data
Improving query performance
Intermediate tables for batch processing
Per-user temporary data management
8-2. Benefits of Temporary Tables
① Improving Query and Performance
Reduces the load of JOIN operations
Pre-aggregating can reduce unnecessary calculations
CREATE TEMPORARY TABLE temp_active_users AS
SELECT id, name FROM users WHERE status = 'active';
SELECT o.order_id, t.name, o.total_price
FROM orders o
JOIN temp_active_users t ON o.customer_id = t.id;
② Temporary Data Storage and Session Management
Automatically deleted when the session ends
Ideal for data that doesn’t need long-term storage
Unaffected by transactions, allowing independent data operations
Example: Temporary Storage of Search Results
CREATE TEMPORARY TABLE temp_search_results AS
SELECT * FROM products WHERE category = 'electronics';
SELECT * FROM temp_search_results;
③ Safe Data Updates
Can be used as an intermediate table for batch processing
Can serve as a backup during data updates
Useful for creating test data
Example: Safe Data Update
CREATE TEMPORARY TABLE temp_orders AS
SELECT order_id, total_price FROM orders WHERE status = 'pending';
UPDATE orders o
JOIN temp_orders t ON o.order_id = t.order_id
SET o.total_price = t.total_price * 1.1;
8-3. Disadvantages and Cautions of Temporary Tables
① Data Disappears When Session Ends
Not suitable for persistent data storage
Since data is lost when the session ends, use regular tables for long-term storage
② Cannot Be Shared Across Sessions
Cannot be accessed from other connections (sessions)
Use> Actually: Use regular tables when sharing data among multiple users
③ Indexes and Constraints Are Not Inherited Automatically
Indexes are not created when using CREATE TEMPORARY TABLE ... AS SELECT ...
Indexes must be added manually
ALTER TABLE temp_users ADD INDEX idx_email (email);
8-4. Tips for Using Temporary Tables Safely
✅ Explicitly drop them when no longer needed
DROP TEMPORARY TABLE IF EXISTS temp_users;
✅ Avoid name collisions with regular tables
Prefix with temp_
CREATE TEMPORARY TABLE temp_users (...);
✅ Design with performance in mind
Consider adjusting tmp_table_size because large tables may be moved to disk
SHOW VARIABLES LIKE 'tmp_table_size';
8-5. Temporary Tables vs. Alternatives (Views & CTEs)
It is also important to consider when to use temporary tables versus views (VIEW) or CTEs (Common Table Expressions).
Method
Characteristics
When to Use
Temporary Table
Deleted when the session ends
When you need to store temporary data
View (VIEW)
Data is retrieved in real time, but performance may degrade
To store queries that are referenced frequently
CTE (WITH clause)
Virtual table valid only within the query
When you want to handle temporary data without creating a temporary table
Conclusion
In this article, we have compiled a comprehensive summary of MySQL temporary table usage points.
✔ Review
MySQL temporary tables are automatically deleted when the session ends
Help optimize performance and reduce the load of JOINs and aggregations
Useful for intermediate data storage in batch processing, temporary storage of search results, and creating test data
However, they cannot be shared across sessions and indexes must be added manually
By choosing between views (VIEW) and CTEs (WITH clause), you can achieve more flexible data management
Now, all sections of the article on MySQL temporary tables are complete! 🎉
Use this article as a reference to effectively leverage MySQL temporary tables.
