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; …”.

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

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

  1. SET tmp_table_size = 512000;

    thanks for this line…
    With some queries this command line works perfect with me.
    But for some other cases no.

  2. 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?

      • mysql> CREATE TABLE new_table SELECT * FROM old_table;This will not always ctaere a new table with the same structure as the old table. It will ctaere a table which fits the data exactly.For example – you have a field which is a VARCHAR(30) but the longest string in that field is only 20 chars long – the new table will be initialised as a VARCHAR(20).The correct procedure would be to ctaere a new table in the likeness of the old:mysql> CREATE TABLE new_table LIKE old_table;and then copying the data in:mysql> INSERT INTO new_table SELECT * FROM old_table;

    • ok watching it happen with mtop… what it does is copy the table as a copy to the tables directory… then uses the temp space (repair by sorting) to do the actual conversion. then renames the converted copied table to the original table name. then deletes the old table.

  3. 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 ?


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

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