Avoid MySQL “Copying to tmp table”

I was recently asked to look into some MySQL performance issues. As expected, they conducted a simple package install and never tinkered with the default configuration. This post does not dig into the multitude of configuration settings with the exception of the tmp_table_size variable. If you have large tables (millions of rows) you will invariably end up wanting to execute a query conducting some sort of DISTINCT, ORDER BY, or GROUP BY function. When this happens, your 10 second query turns into a 5 minute query. This happens because MySQL goes from using a memory table (fast), to converting the memory table to a MyISAM table on disk (slow).

Before we dive into the tmp_table_size setting, check to be sure MySQL is using a temporary table in the first place. Do this by using EXPLAIN on your query. The last column Extra, will say something like “Using temporary; …”.

EXPLAIN SELECT DISTINCT element
FROM database.table
GROUP BY element;

Depending on your MySQL configuration, the use of temporary tables can be a major bottleneck. To increase the temp table space size execute the following SET statement before running your SELECT.

-- set the temp memory size to 512MB
SET tmp_table_size = 512000;

-- now run your SELECT
SELECT DISTINCT account
FROM database.largetable
WHERE account LIKE 'themanbehind%'
ORDER BY account;

This will help keep the processing, sorting, and grouping in memory (fast) and off of disk (slow). Of course the increase is only valid during your connection. If you start a new connection, you have to re-establish the new tmp_table_size value again. If you find yourself constantly increasing this, you may want to think about increasing the default size in your my.conf file.

As with everything, your results may vary so I encourage you to explore different sizes until you find the sweet spot.

9 thoughts on “Avoid MySQL “Copying to tmp table”

  1. I have a table that is 9GB and set the tmp_table_size to 13GB. im trying to change a text field from swedish to utf8_general_ci. still using disk for temp table. any ideas?

  2. Hi

    thanks for this post
    i did what you said but it didnt work for me
    it just freezes on copying to tmp table on disk ?

    thanks

    • How big is your table and what are you trying to do with it? Trouble shooting performance issues can be tricky without the help of additional information.

  3. Hello,
    In the statement

    SET tmp_table_size = 5120000;

    there’s a zero that needs to be erased :)
    Thanks for the post.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>