We all know disk based temporary tables are bad and you should try to have implicit temporary tables created in memory where possible, do to the fact that you should increase tmp_table_size to appropriate value and avoid using blob/text columns which force table creation on the disk because MEMORY storage engine does not support them.
Right?
Wrong.
…is not enough as MySQL also looks at max_heap_table_size variable and uses lower value as a limit to for in memory temporary table after which it will be converted to MyISAM.
To make things more confusing this is not what you would read in MySQL manual as far as I understand it:
From http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
MEMORY table contents are stored in memory, which is a property that MEMORY tables share with internal tables that the server creates on the fly while processing queries. However, the two types of tables differ in that MEMORY tables are not subject to storage conversion, whereas internal tables are:
* If an internal table becomes too large, the server automatically converts it to an on-disk table. The size limit is determined by the value of the tmp_table_size system variable.
* MEMORY tables are never converted to disk tables. To ensure that you don’t accidentally do anything foolish, you can set the max_heap_table_size system variable to impose a maximum size on MEMORY tables. For individual tables, you can also specify a MAX_ROWS table option in the CREATE TABLE statement.
For me, this description looks as there are two types of in-memory tables where internal ones are controlled by tmp_table_size and explicit ones use max_heap_table_size value.
Interesting enough there is 2.5 years old bug on this matter which just recently started to get attention. I understand it could be complex to fix but why real behavior was not documented in the manual at least?
But what surprises me the most is how this issue was fixed (patch pending):
[email protected], 2006-11-16 04:11:16+03:00, [email protected] +6 -0
BUG #4291 fix: new configuration option “disk-tmp-table-size”
introduced to set maximum expected on-disk temporary table size
and avoid mix-up of tmp_table_size and max_heap_table_size[email protected], 2007-01-03 14:45:26+03:00, [email protected] +7 -0
BUG #4291: max_heap_table_size affects creation of disk-based temporary tablefix: the new system variable memory_tmp_table_size is introduced;
it stands now for the exact purpose the Manual says
tmp_table_size used to do.tmp_table_size retains to (give a hint about a)
limit of the on-disk temporary table size. The limit imposed upon
the disk-based temporary tables is still quite relative due to MyISAM
current implementation restrictions.
So now we’re getting 4 variables instead of two?
It is unclear about tmp_table_size – if it is going to be read-only variable to tell you what maximum temporary table size is or is it going to limit on disk table size? Any of behaviors have nothing to do with previous behavior and the second one would break a lot of things.
In my opinion, it would be much better to change it to match what users know about it, what is documented in the manual, config files, tons of books and articles on the web – you will have users expecting old behavior for years.
The size restriction of the on-disk temporary table could be good to add but that should have been another variable.
