Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

What makes mysql create temporary tables on disk?

jrabbitjrabbit ContributorInactive User Role Beginner
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;</pre>

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?


  • nilgirinilgiri Entrant Current User Role Beginner
    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:

    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">
    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?
  • jrabbitjrabbit Contributor Inactive User Role Beginner
    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.
  • lanceaugust31lanceaugust31 Entrant Inactive User Role Beginner
    all English text that has passed through a word processor for smart quotes and lengthened dashes and slanting apostrophe's...

    Writing jobs
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.