MySQL creates temporary table to complete the query. However, it doesn't tell you
whether that temporary table will be in memory or on disk. MySQL's tmp_table_size
variable will control the temporary table size.The default tmp_table_size size is 32 MB
Temporary tables can either be in the Disk or in Memory. If the space required to build
the temporary table is less than or equal to tmp_table_size, MySQL keeps it in memory
rather than incur the overhead and time required to write the data to disk and read it
again. However, if the space required exceeds tmp_table_size, MySQL creates a disk-based
table in its tmpdir directory (often /tmp on Unix systems.) .
mysql> SHOW STATUS LIKE 'Created_tmp_%';
To find out how often that happens, compare the relative sizes of the Created_tmp_tables
and Created_tmp_disk_tables counters.
ViSolve DB Team.
----- Original Message -----
To: "MySql Mail List" <mysql@stripped>
Sent: Thursday, September 07, 2006 8:00 PM
Subject: View hidden temporary files
In http://dev.mysql.com/doc/refman/4.1/en/temporary-files.html says
"MySQL creates all temporary files as hidden files. This ensures that the
temporary files are removed if mysqld is terminated. The disadvantage of
using hidden files is that you do not see a big temporary file that fills up
the filesystem in which the temporary file directory is located."
Is there any form of see the length of the temporary files created by MySQL?