Temporary Tables in MySQL: A High-level Overview
Temporary tables are created automatically and only in certain scenarios. This post explains why MySQL uses temporary tables and how to avoid them.
Join the DZone community and get the full member experience.Join For Free
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:
- We run
ALTER TABLEstatements on huge sets of data (refer to the example above.)
- We run
UPDATEstatements on multiple tables at once.
- We want some
DISTINCTvalues, and we also want them to be ordered in a certain way.
- We want to count
DISTINCTvalues existing in a table.
- 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:
ALTER TABLE demo_table ADD INDEX demo_idx(demo_column);
UPDATE [LOW_PRIORITY] [IGNORE] demo_table, demo_table2 SET demo_table.demo_column = 'Demo Value', demo_table2.column = 'Demo'
SELECT DISTINCT demo_column ORDER BY id;
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:
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
- Create a new table called
- 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 INFILEas well.
- Drop the
DROP TABLE demo_table.
RENAME demo_table_new TO demo_table.
Completing these steps should let MySQL complete such operations faster.
Published at DZone with permission of Everett Berry. See the original article here.
Opinions expressed by DZone contributors are their own.