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. 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. 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.
