MySQL uses internal temporary tables to execute some queries. Usually the tables are stored in memory and on disk if some conditions are met :
Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:
http://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html
This was true until MySQL 5.6.15. A voluntary side effect of this bug fix :
Bug 17566396 – MATERIALIZATION IS NOT CHOSEN FOR LONG UTF8 VARCHAR JOINS
was to loosen the condition on the second item. You can now use up to varchar(512) whatever the character set (utf8, utf8mb4) in group by / distinct and of course in joins. I often see varchar(255), usually in latin1 and more and more in utf8.
Here is an example :
|
1 2 3 4 5 6 7 8 |
USE test; DROP TABLE IF EXISTS t; CREATE TABLE t(txt varchar(255)) ENGINE=InnoDB CHARSET = utf8; INSERT INTO t(txt) VALUES (repeat('0', 255)); FLUSH STATUS; SHOW SESSION STATUS LIKE '%tmp%'; SELECT DISTINCT txt FROM t; SHOW SESSION STATUS LIKE '%tmp%'; |
Result in 5.6.14 :
|
1 2 3 4 5 6 7 8 9 |
mysql> show session status like '%tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 1 | <==== goes to disk ... | Created_tmp_files | 0 | | Created_tmp_tables | 1 | +-------------------------+-------+ 3 rows in set (0.00 sec) |
Result in 5.6.15 :
|
1 2 3 4 5 6 7 8 9 |
mysql> show session status like '%tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | <=== goes to memory ! | Created_tmp_files | 0 | | Created_tmp_tables | 1 | +-------------------------+-------+ 3 rows in set (0.00 sec) |
This is the internal bug for the documentation change request :
Bug 17935006 – INTERNAL TEMPORARY TABLE LIMITATIONS CHANGED IN 5.6.15
A big thank to the Optimizer team for fixing this very ancient restriction !