Temporary Tables in MySQL: A High-level Overview

Anyone who has done substantial work with MySQL has probably noticed how big data affects MySQL databases—most likely some partition nuances or a couple of items related to indexes. However, another important feature offered by MySQL for big data purposes is the ability to create temporary tables. In this blog post, we are going to go into more detail on this subject.
​

What Are Temporary Tables?

In MySQL, a temporary table is a special type of table that (you guessed it) holds temporary data. These kinds of tables are usually created automatically and are typically only considered when certain types of problems arise—for example, when ALTER TABLE statements are run on vast sets of data.

Let’s say we run an ALTER TABLE query to add an index to a table with 100 million records or more. MySQL creates a temporary table (let’s call it temp_table for now) and copies all of the data from the original table (let’s call it demo_table) to the temporary table. Then, it recreates the data from the original table (demo_table) into the temporary table (temp_table) and creates all of the indexes necessary for the demo_table on the temp_table, before swapping the two. Confusing? It shouldn’t be. You see, MySQL does all of these operations to be more efficient! Efficiency is often one of the primary reasons why MySQL DBAs mention temporary tables to their developer colleagues – some of them also note that there is no one way to know when MySQL will create temporary tables, which is not entirely false.
​

When Are Temporary Tables Created?

In MySQL, temporary tables are created when:

1. We run ALTER TABLE statements on huge sets of data (refer to the example above.)
2. We run UPDATE statements on multiple tables at once.
3. We want some DISTINCT values, and we also want them to be ordered in a certain way.
4. We want to count DISTINCT values existing in a table.
5. If we refer to the MySQL documentation, we will see that MySQL uses temporary tables in some other scenarios. ​

Now that you know when temporary tables are in use, we will go through a few examples, shall we? In general, temporary tables would be used by MySQL when we run queries that look something like these:

1. ALTER TABLE demo_table ADD INDEX demo_idx(demo_column);
2. UPDATE [LOW_PRIORITY] [IGNORE] demo_table, demo_table2 SET demo_table.demo_column = ‘Demo Value’, demo_table2.column = ‘Demo’
3. SELECT DISTINCT demo_column ORDER BY id;
4. SELECT id, COUNT(DISTINCT order) FROM demo_table; ​

Anyone who has worked with a MySQL database has almost certainly run at least one of these queries.
​

Avoiding Temporary Tables?

Some MySQL engineers might say that it would be a good idea to prevent temporary tables from being created at all. However, that’s frequently easier said than done – especially if you run database instances on slow disks and (or) with a lot of data. Still, there are a couple of things you can do nonetheless: for example, if you want to get into this, you could use a disk intended as a “RAM disk” and tell MySQL to put all of it its temporary data there. As the disk should be larger than the amount of memory you have, operations should generally complete sooner. Set this parameter to wherever your RAM disk is located, and you’re done:
​
tmpdir = /var/bin/mysql/temp
​
Another way would be to only use necessary data before performing any operations that would need to use temporary tables. For example, if you have, say, a hundred million records or more and you are pretty sure you will not use some of it (say, you will not use data from a specific column, but you are not too sure how to skip this operation, so you load the data into the column anyway), it would probably be feasible to only load data into a specific column rather than all of them at once – for that, you could make use of a feature offered by LOAD DATA INFILE and load data only into one or two columns like so:
​
LOAD DATA INFILE ‘/directory/here/file.txt’ **IGNORE** INTO TABLE demo_table FIELDS TERMINATED BY ‘:’ **(demo_column)**;
​
Keep an eye out on the parts of the query in bold: the IGNORE keyword would ignore any errors and the demo_column part would only load data into one column: demo_column.

And finally, if temporary tables are getting on your nerves, you could also create an empty table, move the data from your table over to it, drop the old table and rename your new table to the name you want to use. For example, if your original table is called demo_table:
​

1. Create a new table called demo_table_new.
2. Manually move the data over from the original table to demo_table_new. For faster and bulk importing, you could make use of LOAD DATA INFILE as well.
3. Drop the demo_table: DROP TABLE demo_table.
4. Rename demo_table_new to demo_table: RENAME demo_table_new TO demo_table.

Completing these steps should let MySQL complete such operations faster.

Lukas is an ethical hacker, a MySQL database administrator, and a frequent conference speaker. Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania and abroad including advertising, gift-buying, gaming, hosting websites as well as some websites of government institutions. Lukas runs one of the biggest & fastest data breach search engines in the world: BreachDirectory.com and frequently blogs in multiple places educating people about information security and other topics. He also runs his own blog over at lukasvileikis.com.