Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
A small optimizer change worth noticing
+4 Vote Up -0 Vote Down

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:

  • Presence of a BLOB or TEXT column in the table

  • Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes

  • Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used

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 :

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 :

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 :

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 !

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.