{"id":95,"date":"2013-12-20T18:26:52","date_gmt":"2013-12-20T18:26:52","guid":{"rendered":"http:\/\/aadant.com\/blog\/?p=95"},"modified":"2013-12-20T18:26:52","modified_gmt":"2013-12-20T18:26:52","slug":"a-small-optimizer-change-worth-noticing","status":"publish","type":"post","link":"http:\/\/aadant.com\/blog\/2013\/12\/20\/a-small-optimizer-change-worth-noticing\/","title":{"rendered":"A small optimizer change worth noticing"},"content":{"rendered":"<p>MySQL uses internal temporary tables to execute some queries. Usually the tables are stored in memory and on disk if some conditions are met :<\/p>\n<blockquote><p>Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:<\/p><\/blockquote>\n<div>\n<ul>\n<li>\n<blockquote><p>Presence of a <a title=\"11.4.3.\u00a0The BLOB and TEXT Types\" href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/blob.html\"><code>BLOB<\/code><\/a> or <a title=\"11.4.3.\u00a0The BLOB and TEXT Types\" href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/blob.html\"><code>TEXT<\/code><\/a> column in the table<\/p><\/blockquote>\n<\/li>\n<li>\n<blockquote><p>Presence of any string column in a <code>GROUP BY<\/code> or <code>DISTINCT<\/code> clause larger than 512 bytes<\/p><\/blockquote>\n<\/li>\n<li>\n<blockquote><p>Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the <a title=\"13.2.9.\u00a0SELECT Syntax\" href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/select.html\"><code>SELECT<\/code><\/a> list, if <a title=\"13.2.9.4.\u00a0UNION Syntax\" href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/union.html\"><code>UNION<\/code><\/a> or <a title=\"13.2.9.4.\u00a0UNION Syntax\" href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/union.html\"><code>UNION ALL<\/code><\/a> is used<\/p><\/blockquote>\n<\/li>\n<\/ul>\n<\/div>\n<p><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/internal-temporary-tables.html\">http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/internal-temporary-tables.html<\/a><\/p>\n<p>This was true until MySQL 5.6.15. A voluntary side effect of this bug fix :<\/p>\n<p><b>Bug 17566396 &#8211; MATERIALIZATION IS NOT CHOSEN FOR LONG UTF8 VARCHAR JOINS<br \/>\n<\/b><\/p>\n<p>was to loosen the condition on the second item. <strong>You can now use up to varchar(512) <em>whatever the ch<\/em><em>aracter set<\/em> (utf8, utf8mb4) in group by \/ distinct and of course in joins<\/strong>. I often see varchar(255), usually in latin1 and more and more in utf8.<\/p>\n<p>Here is an example :<\/p>\n<pre class=\"lang:mysql decode:true\" title=\"Example test case\">USE test;\r\nDROP TABLE IF EXISTS t;\r\nCREATE TABLE t(txt varchar(255)) ENGINE=InnoDB CHARSET = utf8;\r\nINSERT INTO t(txt) VALUES (repeat('0', 255));\r\nFLUSH STATUS;\r\nSHOW SESSION STATUS LIKE '%tmp%';\r\nSELECT DISTINCT txt FROM t;\r\nSHOW SESSION STATUS LIKE '%tmp%';<\/pre>\n<p><strong>Result in 5.6.14 :<\/strong><\/p>\n<pre class=\"lang:mysql mark:5 decode:true\">mysql&gt; show session status like '%tmp%';\r\n+-------------------------+-------+\r\n| Variable_name           | Value |\r\n+-------------------------+-------+\r\n| Created_tmp_disk_tables | 1     |   &lt;==== goes to disk ...\r\n| Created_tmp_files       | 0     |\r\n| Created_tmp_tables      | 1     | \r\n+-------------------------+-------+\r\n3 rows in set (0.00 sec)<\/pre>\n<p><strong>Result in 5.6.15 :<\/strong><\/p>\n<pre class=\"lang:mysql mark:5 decode:true crayon-selected\" title=\"5.6.15\">mysql&gt; show session status like '%tmp%';\r\n+-------------------------+-------+\r\n| Variable_name           | Value |\r\n+-------------------------+-------+\r\n| Created_tmp_disk_tables | 0     | &lt;=== goes to memory !\r\n| Created_tmp_files       | 0     |\r\n| Created_tmp_tables      | 1     |\r\n+-------------------------+-------+\r\n3 rows in set (0.00 sec)<\/pre>\n<p>This is the internal bug for the documentation change request :<\/p>\n<p><b>Bug 17935006 &#8211; INTERNAL TEMPORARY TABLE LIMITATIONS CHANGED IN 5.6.15<br \/>\n<\/b><\/p>\n<p>A big thank to the Optimizer team for fixing this very ancient restriction !<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &hellip; <a href=\"http:\/\/aadant.com\/blog\/2013\/12\/20\/a-small-optimizer-change-worth-noticing\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[6],"tags":[],"class_list":["post-95","post","type-post","status-publish","format-standard","hentry","category-tip"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/posts\/95","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/comments?post=95"}],"version-history":[{"count":8,"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/posts\/95\/revisions"}],"predecessor-version":[{"id":103,"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/posts\/95\/revisions\/103"}],"wp:attachment":[{"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/media?parent=95"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/categories?post=95"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/aadant.com\/blog\/wp-json\/wp\/v2\/tags?post=95"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}