On 9/2/2010 1:39 PM, neutron wrote:
Hello Johan,
Thanks for the reply.
On Thu, Sep 2, 2010 at 3:34 AM, Johan De Meersman <vegiv...@tuxera.be> wrote:
I suspect he is talking about the Temp Tablespace concept from Oracle, which
is different from a temporary table or a memory table.
MySQL will allocate a memory table for sort operation and the like, up until
that table exceeds a preset limit, at which point it will automatically (and
costly !) be converted to a disk table.
==>
How to define the memory table limit?
When the temp table is converted to a disk table, where is this disk
table stored? In the same shared tablespace file if I don't use
"innodb_file_per_table"?
The automatically-converted tables produced by the system as part of SQL
command processing start off as MEMORY tables unless they contain data
that the MEMORY storage engine does not support. If they do contain
unsupported data types or if they exceed the size of the smaller of
--max-heap-table-size or --tmp-table-size, then the table is converted
to a MYISAM table.
The folder for temporary tables is controlled by the --tmpdir parameter.
This behavior and the configuration variables I discussed are covered in
more detail in these links:
http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html
http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_tmpdir
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_size
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
