This article was written by a co-worker of mine and I wanted to share with everyone else.

Temporary Tables. What are they? Why are they bad? You might think you don’t explicitly use any, but in actuality you may be using them all the time.

Temporary tables come in two flavors. The kind you know you’re making, and the kind you don’t. You know you’re making a temporary table when you use the CREATE TEMPORARY TABLE syntax. On the other hand, there are lots of other cases in which MySQL will create a temporary table for you, in the background, to help out the query execution to get you your results.

Lets first talk about why implicit temporary tables are bad ( when you use CREATE TEMPORARY TABLE ). The first major issue, is the risk of breaking replication. An explicit temporary table is viewable only to your connection ID, and when your connection terminates, so does the table. Therefore, should the slave server blip, or should there be a disconnect during replication, it’s possible for the temporary table that your subsequent queries depend on to be gone. This would not make for a very healthy consistent replication slave. Furthermore, the process of creating and destroying tables is not necessarily the most inexpensive procedure. It’s relatively easy to write an application that uses temporary tables, and not realize that every page load is going to write and destory temporary tables.

MySQL also generates its’ own temporary tables, behind your back. These are also generally bad. You can tell if MySQL is doing this by running an EXPLAIN on your query. If MySQL is creating temporary tables behind your back, then the words ‘Using temporary’ will appear in the extra column of the output. These are the cases in which MySQL may do this:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table.

DISTINCT combined with ORDER BY may require a temporary table.
(source: 7.5.10. How MySQL Uses Internal Temporary Tables)

In addition, what’s even worse is when MySQL opts to use an on disk temporary table, in these circumstances.

Presence of a BLOB or TEXT column in the table.

Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes.

Presence of any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL is used.
(source: 7.5.10. How MySQL Uses Internal Temporary Tables)

Writing to disk can generally be avoided by some good indexes, rewriting the query, forcing the join order, or telling MySQL that it’s ‘tmpdir’ is a path on your server that is mounted in memory. These operations are still slower than if you spend a little extra time writing better queries or better schema in the first place.

Check out these Related Posts