MySQL Temporary Table: The No-Fuss Guide on How to Use Them


A MySQL temporary table is a special table that exists only in the current session. It disappears once your MySQL session expires or if you DROP it explicitly. Other sessions cannot use or even see it.


It’s like a whiteboard in a meeting room.


Anything you write on a whiteboard is for the duration of the meeting. You can write down ideas, draw diagrams, and perform calculations. It gives you a work area for writing, erasing, and rewriting ideas. Others outside the meeting room cannot see what’s on your whiteboard. Once the meeting is over, you achieve your purpose with the whiteboard, and you can erase everything.


MySQL temporary tables function as a temporary storage of intermediate results as you process your data. You can use it in scripts, stored procedures, and any series of SQL statements whenever it’s applicable.


Just like good, old regular tables, you can CREATE it, then SELECT, INSERT, UPDATE, DELETE, and more. After you’re done, you can DROP it. Before we proceed with examples, let’s discuss what defines a temporary table.


What Defines a MySQL Temporary Table


MySQL temporary tables have distinct features that sets them apart from regular tables. The following are the things that are special about them:


  • Creation: You can make a temporary table using the CREATE TEMPORARY TABLE statement. Though there’s another secret where you can create it without even knowing it. You’ll see it later.

  • Scope: It’s visible only to the session where you created it.

  • Naming: You can name temporary tables just like regular tables. But you can use the same name in different sessions without MySQL flagging you with errors. You can even use the name of an existing regular table in the database. But doing this will hide the regular table until you DROP the temporary table with the same name.

  • Lifecycle: Temporary tables disappear when you disconnect from your MySQL server or when you explicitly issue a DROP statement.

  • Relationship: Temporary tables have a fuzzy relationship with your MySQL database and the tables within it. For example, even if you drop your database, the temporary table does not drop automatically. And you can’t create a foreign key relationship with a temporary table.

Let’s prove some of these by example.


How to Create and Manage a MySQL Temporary Table Structure


Creating a MySQL temporary table uses the CREATE TEMPORARY TABLE statement. See the syntax below:


CREATE TEMPORARY TABLE [IF NOT EXIST] <table_name>
(column1 type[(size)] [NOT NULL] [PRIMARY KEY] [AUTO_INCREMENT],
 column2 type[(size)] [NOT NULL] [DEFAULT <expr>],
 ...
);

Just like regular tables, you can do the following:


  • Add the optional IF NOT EXIST to ensure the creation will not issue an error if the table name exists.

  • Specify a <table name>.

  • Add columns with their types and optional sizes.

  • Make columns NOT NULLable or with DEFAULT values.

  • Add a PRIMARY KEY with an auto-incrementing value (AUTO_INCREMENT).

You can also create a new temporary table from a SELECT statement. See the syntax below:


CREATE TEMPORARY TABLE <table_name>
SELECT * FROM <another_table>;

Examples

Below is an example of creating a new temporary table:


CREATE TEMPORARY TABLE IF NOT EXIST tmpTable
(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(30) NOT NULL);

You can also use ALTER TABLE to add columns and more (except FOREIGN KEY). Check out the example below for adding a new column in the same temporary table:


ALTER TABLE tmpTable
  ADD COLUMN age SMALLINT;

Another example that will boost query performance for the temporary table is to add an index. See an example below:


CREATE INDEX idx_tmpTable_name
ON tmpTable (name);

And MySQL will use the index when appropriate. See an example below where a SELECT statement is used on the same temporary table. We’re going to use dbForge Studio for MySQL‘s Query Profiler. Note the EXPLAIN results below.




Meanwhile, the below example will cause an error:


-- create the first temporary table from the sakila actor table
CREATE TEMPORARY TABLE tmpActor
SELECT * FROM actor;

-- create the second temporary table from the sakila film_actor table
CREATE TEMPORARY TABLE tmpFilmActor
SELECT * FROM film_actor;

-- add a foreign key constraint for the 2 temporary tables - THIS WILL TRIGGER AN ERROR
ALTER TABLE tmpFilmActor
	ADD FOREIGN KEY tmpForeignKey (actor_id) REFERENCES tmpActor(actor_id);

The first two statements above are valid but adding the foreign key will fail.


Managing Data on a MySQL Temporary Table


Temporary tables are not different from regular tables when adding, updating, deleting, and querying data.


Check out the following example where we use INSERT on the same temporary table we used earlier:


INSERT INTO tmpTable 
(name)
VALUES ('UZUMAKI NARUTO'), ('UCHIHA SASUKE'), ('HARUNO SAKURA');

The above will insert 3 names on tmpTable.


Then, you can check out if the INSERT was successful by using a SELECT statement. See a sample below with the results.



The above shows the inserted rows in the result set.


Meanwhile, the age column is all ugly with NULLs. We can use an UPDATE statement to fix this. See an example below:



There it is. The first row has a non-null value for the age column.


Then, you can delete any of the rows in a temporary table just like a regular table. The following is an example: 


DELETE FROM tmpTable
WHERE id = 2;

Finally, the screenshot below shows querying the same temporary table in another session. It causes an error proving that temporary tables are only accessible from the session that created them.



Dropping Temporary Tables


You can explicitly delete temporary tables using the DROP TABLE command. See the syntax below:


DROP TABLE <temporary_table_name>;

The <temporary_table_name> is the name of the temporary table to delete.


Here is an example using the temporary table earlier:


DROP TABLE tmpTable;

Internal MySQL Temporary Tables – Temp Tables Hiding Behind Your Back


You may have used temporary tables before without knowing of it. MySQL creates them behind the scenes when necessary. These are known as internal temporary tables. And you have no direct control over when MySQL will create them. Queries with ORDER BY, GROUP BY, or using aggregate functions like SUM and AVG will trigger the creation of at least one temporary table.


Check out an example below where ORDER BY, GROUP BY, and COUNT were used in the query. The EXPLAIN results in the dbForge Studio Query Profiler shows it.



Did you see the Extra column where it says “Using temporary”? That’s your internal temporary table. Here’s another proof but this time in the Session Statistics:



When summarizing and aggregating large datasets, MySQL may need a large temporary table that will consume disk space and RAM. Always check the EXPLAIN results and other performance metrics and simplify your query (or consider alternatives).


Are Using MySQL Temporary Tables a Bad Practice?


No, they’re not.

But like any other tool, how and when you use them matters a lot. Are you going to use a hammer to cut wood? Maybe it can cut, but not the results you want.


Temporary tables are the same. Using them in the wrong places is bad news.

The next sections are worth your consideration.


When Temporary Tables Are Useful


Below are some scenarios where temporary tables make sense.


1. Breaking Down Complex Queries


Complex queries can be slow because you guessed it – it’s complex. If your SELECT statement includes joins with a mix of subqueries, derived tables, and more, you can break down your single query into smaller chunks. Then, use one or more temporary tables to store the results of each.


But note that this is not an absolute rule. You need to check if breaking them down will increase script performance. Most of the time they do. But if not, you may consider other alternatives like a common table expression or leave it as is.


2. Storing Intermediate Results of a Repetitive Query


Sometimes, you form a query with repetitive parts, like subqueries. So, MySQL will perform the same subquery more than once. To avoid that, you can create a temporary table that will return the subquery results. Then, join it to the main query.


Another scenario is a series of statements where a subquery is repeated in the WHERE clauses. Check an example below:



You can remove the repetition and avoid processing them twice using a temporary table. See the adjusted code below:



3. Performance Optimization


By using a temporary table for repeated calculations or subqueries, you can boost query execution. Add an index for bigger intermediate results and make it even faster. Check the previous example under Storing Intermediate Results of a Repetitive Query again. Because of removing the repeated subquery, the execution time became faster. See the status bar of the screenshots. The first has an execution time of 00:00:00.144. After removing the repetition, the second screenshot shows an execution time of 00:00:00.098.


Potential Pitfalls and Considerations


  1. Resource Consumption: Watch out for temporary tables storing very large intermediate results. This will consume more RAM and may spill onto the disk. Monitor the size of your intermediate results to avoid this problem.

  2. Overuse: Simplifying queries with temporary tables is good as long as it is not overused. Overusing can lead to more complex scripts and defeat the purpose of simplifying. It will also consume more compute resources.

Best Practices


  1. Optimize Usage: Use indexes on large temporary tables especially if you want to join it with other tables or use it on a subquery. Then, drop them when you’re done to free up resources quickly.

  2. Monitor Performance: Keep an eye on the RAM and disk usage of your temporary tables. This will affect your MySQL server performance. Optimize them when needed.

  3. Use Alternatives When Appropriate: For simple cases, a common table expression (CTE) may be better and faster than temporary tables. Or use a simple, one-time subquery if this is proven faster. In any case, fine-tune the performance using tools like the Query Profiler of dbForge Studio for MySQL. This will determine which of your alternatives will be faster.

Conclusion


MySQL temporary tables can be handy for storing temporary data or intermediate results. But use them with caution because too much can be bad for your server. And there may be better alternatives like common table expressions, subqueries, or joins.


Also, be aware of internal temporary tables when using aggregate functions, grouping, or ordering results. Make your queries simpler to avoid internal temporary tables or you may need to break down complex ones.


Knowing if temporary tables are faster than CTE or other alternatives needs a top-notch performance tuning tool. Devart’s dbForge Studio for MySQL has the Query Profiler to help you fine-tune your queries and choose the best option. Try dbForge Studio for MySQL by downloading it today and boost your productivity in all your MySQL development efforts.


Related Posts:


How To Create MySQL Data Backup Automatically

Automation of Backup and Restore of MySQL Databases Using SuiteCRM or SugarCRM