Copying to tmp table

Copying to tmp table is a common thread state within MySQL which occurs when the MySQL server is copying to a temporary table in memory.

MySQL will need to create temporary tables under certain circumstances e.g.:

1. Sorting operations e.g. if a query contains an "Order By" clause, or "Group By" clause
2. If the query contains "distinct"

To determine whether a query requires a temporary table, use EXPLAIN and check the Extra column to see whether it says "Using temporary".

According to the MySQL site:

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

• 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

Queries which involve disk based Temporary tables are often slow, and a large number of queries of this type can result in a serious performance bottleneck.

The example below show's how the same query performs when it uses a memory based temporary table, and then how it performs using a disk based temporary table.

N.B. this is for indicative purposes only, you probably don't want to set such a low value for tmp_table_size on your MySQL Server!
N.B. the query cache has been disabled for the purposes of the test

Our query, which will produce a list of statistic_id and the summation of their values. The group by means that MySQL will use a temporary table in order to sort and process the results.

SELECT statistic_id, sum(statistic_value)
FROM statistics
WHERE statistic_type = 'mysql'
GROUP BY statistic_id;

Average response time for the query is 0.23 seconds

Now for the second test we will reduce the size of tmp_table_size to just 1k, which will mean that our MySQL server does not have adequate space in memory to process the query.

mysql> set tmp_table_size = 1024;
Query OK, 0 rows affected (0.00 sec)

This time the query now takes 1.5 seconds (650% slower!) to execute which is much longer than before, due to the fact that MySQL now needs to create a temporary table on disk.

1. So... the bottom line is:
   Temporary tables are used frequently in MySQL, and can be a source of bottlenecks particularly if the tables need to be created on disk.
2. Although the above example shows you how you can quickly degrade performance by reducing the size of tmp_table_size, the inverse could be true if your MySQL Server tmp_table_size value is configured too low i.e. you could dramitically improve performance and throughput by increasing the value
3. You need to monitor to get visbility into exactly what it occuring.