I've got a web application connected to a database running MySQL.

Here's the problem: a not insignificant number of queries create temporary tables and many of the tables involved have BLOB fields. This means that I'm getting lots of temporary tables being created on disk (this behavior is described here https://dba.stackexchange.com/a/17679/16724) and this results in pretty crappy performance.

Is there by any chance an easy solution to this problem? Perhaps some fork or other pluggable engine? I'm actually running the Percona fork of MySQL (XtraDB), but it's the same problem with vanilla InnoDB. I'm frankly considering switching to Postgres.


  • Internal temporary tables used in processing queries are created by the MySQL layer, not the storage engine. They are always handled by the MEMORY engine when they can be, and are handled by MyISAM when they can't, no matter which engine(s) are providing the rows that are going into the internal temporary table. The best solution is to optimize queries to avoid temporary tables when possible. Commented Jan 4, 2013 at 6:39

1 Answer 1


You need to properly tune all connection-based settings as you pointed out from that link.

If you are looking for something quick and dirty, I have just the thing:

Back on Nov 30, 2012, I wrote a nice post about mapping temp tables to RAM, even disk-based temp tables : Is it bad to create many mysql temporary tables simultaneously?

Give it a Try !!!

UPDATE 2013-01-03 22:14 EDT

In terms of the BLOBs, my condolences. Notwithstanding, I have good news. I have a very quick-and-dirty stunt you can do once you set up the RAM disk.

Ready ???

Set the following values in

tmp_table_size = 1K
max_heap_table_size = 1K

That's not a misprint. That's not a typo. I said set tmp_table_size and max_heap_table_size to 1K. You are probably saying "Why set those values so low?"

Those values represent the largest in-memory temp table allowed before transferring the temp table to disk. If you have a RAM disk, then temp tables leap right into the RAM disk as quickly as possible. As the MySQL Documentation says on max_heap_table_size:

If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

  • Are there connection settings that are relevant to tmp tables with BLOBs (as opposed to just being too large)? RAM Drive is a great suggestion though, thanks!
    – Eli
    Commented Jan 4, 2013 at 2:58
  • I'm guessing a RAM drive to temp files would be really bad if it doesn't have enough space, right?
    – Eli
    Commented Jan 4, 2013 at 3:00
  • SELECT SQL_BIG_RESULT ... can also direct the server to bypass MEMORY tables and go straight to disk-based tables on demand. Commented Jan 4, 2013 at 6:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.