What makes mysql create temporary tables on disk?

I’ve long suspected that the ‘tmp_table_size’ setting in mysql does not work, and temporary tables that are clearly going to fit in memory are created on disk anyway. I’ve just discovered a simple test case:

SET GLOBAL tmp_table_size=33554432;CREATE TABLE test ( id int(11) NOT NULL, name varchar(512) NOT NULL, description varchar(513) NOT NULL) ENGINE=MEMORY DEFAULT CHARSET=latin1;INSERT INTO test VALUES (1,‘a’,‘b’);show status like ‘cre%’;(SELECT name FROM test WHERE id=1)UNION (SELECT name FROM test WHERE id=2);show status like ‘cre%’;(SELECT description FROM test WHERE id=1)UNION (SELECT description FROM test WHERE id=2);show status like ‘cre%’;DROP TABLE test;

The first of these queries that returns the letter ‘a’ from a varchar(512) column creates the temporary table in RAM, but the second that selects the letter ‘b’ from a varchar(513) column creates the temporary table on disk. It isn’t a limitation of memory tables, as the original table itself is created with that strorage engine, so why does the 1 byte larger column size make mysql think the results of this query won’t fit in 32Mb, despite the source table only having 1 record?

We have been recently struggling with disk temp tables as well, and I came across MySQL 5.1 Reference Manual :: 6 Optimization :: 6.5 Optimizing the MySQL Server :: 6.5.8 How MySQL Uses Internal Temporary Tables which lists this related cause for tmp tables going to disk:

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

Apparently, unions will do it too. I think this manual page could use a great deal more work, particularly in the “why” department, but I’ll settle for more on the “what”. Maybe a comment there on this point would help. Is there anything else you have found related to tmp tables that is not covered on that page?

I reported it as a bug, and they said they will update that manual page to include UNIONs, but they were not forthcoming with an answer to “why”

Also, the manual is inaccurate - it seems to be 512 characters rather than 512 bytes that is significant. If in my example you declare the character set to be utf-8, then both columns can potentially be more than 512 bytes long, but it is still only the 513 character column that generates a disk table.

all English text that has passed through a word processor for smart quotes and lengthened dashes and slanting apostrophe’s…

Writing jobs