What are Temporary Tables?

The Temporary Tables are the tables that are created in a database to store data temporarily. These tables will be automatically deleted once the current client session is terminated or ends. In addition to that, these tables can be deleted explicitly if the users decide to drop them manually.

Query:

Though MySQL automatically removes temporary tables when your database connection ends, we can still delete them ourselves by using the DROP TEMPORARY TABLE command if we want to.

Difficulty: 6/10

Topics: Session Scope, In-Memory vs Disk, Connection Pooling

Scenario Questions

0-2 years experience

1
We have a multi-step reporting script where we need to store some intermediate user metrics temporarily. If we use 'CREATE TEMPORARY TABLE', what happens to that data if the script crashes halfway through? Do we need to write a cleanup script to drop it?
2
Imagine you create a temporary table named 'temp_users' in your database GUI client to run some test queries. If your teammate logs into the exact same database from their machine, can they see or query your 'temp_users' table? Why or why not?

2-5 years experience

1
We recently migrated our Node.js backend to use a connection pool. Since then, we've noticed intermittent 'Table already exists' errors when running a background job that creates a temporary table, even though the job is supposed to start fresh. What do you think is happening here, and how would you fix it?
2
We have a query that creates a temporary table to process daily logs. It runs extremely fast during local testing with 100 rows, but in production with 100,000 rows, it suddenly crawls and causes high disk I/O. What MySQL configurations or table behaviors should we investigate?

5-8 years experience

1
We are designing a high-throughput data ingestion pipeline in MySQL. One proposal is to use temporary tables for staging data before merging it into the main tables. How would this approach impact our replication lag on the read replicas, especially considering the differences between Statement-Based and Row-Based replication?
2
In MySQL 8.0, the default engine for internal temporary tables changed from MEMORY to TempTable. If we are running complex queries with large 'GROUP BY' or 'DISTINCT' clauses that exceed 'temptable_max_ram', how does MySQL handle the overflow, and what are the architectural implications on our storage subsystem?

8+ years experience

1
We are refactoring a legacy monolithic application where multiple services share a single MySQL database and heavily rely on complex stored procedures utilizing temporary tables for session-state management. How would you decouple this architecture to move towards stateless services, and what are the risks of keeping this logic in the database layer?
2
At our scale, we are experiencing severe lock contention and temp-space exhaustion on our database cluster during peak hours due to ad-hoc reporting queries generating massive temporary tables. What architectural alternatives, such as read replicas, OLAP engines, or application-side processing, would you propose to eliminate this dependency, and how would you phase the migration?

Follow-up Questions

❓What happens to a temporary table if the client connection is returned to a connection pool without being explicitly closed?
❓How does MySQL 8.0's TempTable storage engine differ from the traditional MEMORY engine when handling overflow?
❓Under what conditions will MySQL's optimizer implicitly create an internal temporary table on disk instead of in memory?