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?