# tmp\_table\_size ignored? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [November 7, 2006, 7:14pm UTC](https://forums.percona.com/t/tmp-table-size-ignored/116 "2006-11-07T19:14:19Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\ **Post date:** [November 7, 2006, 7:14pm UTC](https://forums.percona.com/t/tmp-table-size-ignored/116/1 "2006-11-07T19:14:19Z")
It looks like no matter what I do, one of my queries is always going to disk to create its temp file, instead of creating one in memory, and this is killing my server’s performance. I have an 8G machine running MySQL 4.1.16, and I’ve set my tmp\_table\_size=64M and my max\_heap\_table\_size=64M. I’ve verified these settings using show variables. I’m checking to see if temp files are created by watching Created\_tmp\_disk\_tables and the temp data directory. Temp files are always created, the largest being 20M in my tests, which is way under my settings (and in test I’ve bumped both the of the table settings from 64M all the way to 4G with no change in behavior). My query doesn’t use BLOB or TEXT fields at all, but is grouping by a VARCHAR. I’ve read on the forums that VARCHAR is automatically converted to CHAR for HEAP tables, with the cost being a much larger temp table due to the fixed size conversion. Even if I restrict my query to a tiny range of data (like 100 rows between the two tables I’m joining), so that the result set is under 10 rows, it will still create a temp file on disk. If I change the query to group by an unindexed integer id in one of these tables, this query NEVER creates a disk temp table, even if I increase the source data range to be 10x the size of my original test. Basically, it looks like grouping by varchar equals disk based temp tables. Is anyone else seeing this? On the bright side, using tmpfs does help the performance considerably, but it’s not my preferred solution (and neither is normalizing my character data by some integer id). ---
**Author:** ![inner](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/inner/32/16878_2.png) [@inner](https://forums.percona.com/u/inner)\ **Post date:** [November 8, 2006, 1:11am UTC](https://forums.percona.com/t/tmp-table-size-ignored/116/2 "2006-11-08T01:11:08Z")
Hi, Vince I can only confirm I’m having the same problem (and if I’m not mistaken - with MySQL 5.0 too). This actually is pretty strange and I could not find any better solution than using tmpfs yet… I’d appreciate any hints too. ---
**Author:** ![toasty](https://avatars.discourse-cdn.com/v4/letter/t/22d042/32.png) [@toasty](https://forums.percona.com/u/toasty)\ **Post date:** [November 8, 2006, 3:48am UTC](https://forums.percona.com/t/tmp-table-size-ignored/116/3 "2006-11-08T03:48:58Z")
Hi Folks, Temp tables are always created on disk if they use columns types that the in-memory temp table type (HEAP/MEMORY) doesn’t support. Let me see if I can find something on this… OK, so this [URL=“http://[MySQL :: MySQL 8.0 Reference Manual :: 8.12.3.1 How MySQL Uses Memory](http://dev.mysql.com/doc/refman/4.1/en/memory-use.html)”][http://dev.mysql.com/doc/refman/4.1/en/memory-use.html[/URL]](http://dev.mysql.com/doc/refman/4.1/en/memory-use.html%5B/URL%5D) page says: “Temporary tables with a large row length (calculated as the sum of all column lengths) or that contain BLOB columns are stored on disk.” No idea what ‘large’ means in this case though. There is a better explanation of this online somewhere, can’t find it just at the mo though… The trick (apart from putting your tmp dir on a ramdisk) is to try to remove blobs etc from getting in your tmp tables as part of the query. Usually not possible though I know… HTH, Toasty ---
**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\ **Post date:** [November 12, 2006, 10:18pm UTC](https://forums.percona.com/t/tmp-table-size-ignored/116/4 "2006-11-12T22:18:56Z")
Can you create repeatable example for this problem ? It is possible it is simply some kind of bug, or it is possible there is something which causes your query to implicitely use TEXT/BLOB result column.