You are here

Avoid temporary disk tables with MySQL

For processing SELECT queries MySQL needs some times the help of temporary tables. These temporary tables can be created either in memory or on disk.

The number of creations of such temporary tables can be found with the following command:

mysql> SHOW GLOBAL STATUS LIKE 'created_tmp%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 4     |
| Created_tmp_tables      | 36    |
+-------------------------+-------+

There are 2 different reasons why MySQL is creating a temporary disk table instead of a temporary memory table:
  • The result is bigger than the smaller one of the MySQL variables max_heap_table_size and tmp_table_size.
  • The result contains columns of type BLOB or TEXT.
In the following example we can see how the temporary disk table can be avoided without changing the column types:
mysql> CREATE TABLE test (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, data TEXT
, type TINYINT UNSIGNED
);

mysql> INSERT INTO test VALUES (NULL, 'State is green', 1), (NULL, 'State is green', 1)
, (NULL, 'State is red', 3), (NULL, 'State is red', 3)
, (NULL, 'State is red', 3), (NULL, 'State is orange', 2);

mysql> EXPLAIN SELECT data, COUNT(*) FROM test GROUP BY data;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

mysql> SHOW SESSION STATUS LIKE 'created_tmp%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 3     |
+-------------------------+-------+

mysql> SELECT data, COUNT(*) FROM test GROUP BY data;
+-----------------+----------+
| data            | count(*) |
+-----------------+----------+
| State is green  |        2 |
| State is orange |        1 |
| State is red    |        3 |
+-----------------+----------+

mysql> SHOW SESSION STATUS LIKE 'created_tmp%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_tables      | 4     |
+-------------------------+-------+

mysql> SELECT SUBSTR(data, 1, 32), COUNT(*) FROM test GROUP BY SUBSTR(data, 1, 32);
mysql> SHOW SESSION STATUS LIKE 'created_tmp%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_tables      | 5     |
+-------------------------+-------+

This method can be used if changing the table structure from TEXT to VARCHAR or the use of a RAM disk are not possible solutions.

Taxonomy upgrade extras: