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

• Sorting operations e.g. if a query contains an "ORDER BY" clause, or "GROUP BY" clause

• 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 shows how the same query performs when it uses a memory based temporary table, and then how it performs using a disk based temporary table.

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! The query cache has been disabled for the purposes of the test.

The following query will produce a list of statistic_id and the summation of their values. The GROUP BY directs MySQL to use a temporary table 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.

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.

• 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 dramatically improve performance and throughput by increasing the value.

• You need to monitor to get visibility into exactly what it occurring.