Temporary tables are created automatically and only in certain scenarios. This post explains why MySQL uses temporary tables and how to avoid them …<\/b>
\nJoin the DZone community and get the full member experience. Anyone who has done substantial work with MySQL has probably noticed how big data affects MySQL databases \u2014 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. 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 \u2014 some of them also note that there is no one way to know when MySQL will create temporary tables, which is not entirely false. In MySQL, temporary tables are created when: 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: Anyone who has worked with a MySQL database has almost certainly run at least one of these queries. 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 \u2014 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: 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 \u2014 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: 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: 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.<\/p>\n