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

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:

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: