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.