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?

Comments

  • 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
    </td></tr></table>

    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.