# What makes mysql create temporary tables on disk? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [December 19, 2007, 12:54pm UTC](https://forums.percona.com/t/what-makes-mysql-create-temporary-tables-on-disk/572 "2007-12-19T12:54:38Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![jrabbit](https://avatars.discourse-cdn.com/v4/letter/j/a587f6/32.png) [@jrabbit](https://forums.percona.com/u/jrabbit)\ **Post date:** [December 19, 2007, 12:54pm UTC](https://forums.percona.com/t/what-makes-mysql-create-temporary-tables-on-disk/572/1 "2007-12-19T12:54:38Z")
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? ---
**Author:** ![nilgiri](https://avatars.discourse-cdn.com/v4/letter/n/2acd7d/32.png) [@nilgiri](https://forums.percona.com/u/nilgiri)\ **Post date:** [December 20, 2007, 10:40am UTC](https://forums.percona.com/t/what-makes-mysql-create-temporary-tables-on-disk/572/2 "2007-12-20T10:40:20Z")
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: | [B]Quote:[/B] | | 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? ---
**Author:** ![jrabbit](https://avatars.discourse-cdn.com/v4/letter/j/a587f6/32.png) [@jrabbit](https://forums.percona.com/u/jrabbit)\ **Post date:** [December 20, 2007, 10:49am UTC](https://forums.percona.com/t/what-makes-mysql-create-temporary-tables-on-disk/572/3 "2007-12-20T10:49:45Z")
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. ---
**Author:** ![lanceaugust31](https://avatars.discourse-cdn.com/v4/letter/l/50afbb/32.png) [@lanceaugust31](https://forums.percona.com/u/lanceaugust31)\ **Post date:** [September 1, 2009, 2:06pm UTC](https://forums.percona.com/t/what-makes-mysql-create-temporary-tables-on-disk/572/4 "2009-09-01T14:06:35Z")
all English text that has passed through a word processor for smart quotes and lengthened dashes and slanting apostrophe’s… * * * Writing jobs