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

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

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.