A small optimizer change worth noticing

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 :

Result in 5.6.14 :

Result in 5.6.15 :

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 !

This entry was posted in tip. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *