Where MySQL Stores Temporary Files
----------------------------------
MySQL uses the value of the `TMPDIR' environment variable as the
pathname of the directory in which to store temporary files. If you
don't have `TMPDIR' set, MySQL uses the system default, which is
normally `/tmp' or `/usr/tmp'. If the file system containing your
temporary file directory is too small, you should edit `safe_mysqld' to
set `TMPDIR' to point to a directory in a file system where you have
enough space! You can also set the temporary directory using the
`--tmpdir' option to `mysqld'.
MySQL creates all temporary files as hidden files. This ensures that
the temporary files will be removed if `mysqld' is terminated. The
disadvantage of using hidden files is that you will not see a big
temporary file that fills up the file system in which the temporary
file directory is located.
When sorting (`ORDER BY' or `GROUP BY'), MySQL normally uses one or two
temporary files. The maximum disk-space needed is:
(length of what is sorted + sizeof(database pointer))
* number of matched rows
* 2
`sizeof(database pointer)' is usually 4, but may grow in the future for
really big tables.
For some `SELECT' queries, MySQL also creates temporary SQL tables.
These are not hidden and have names of the form `SQL_*'.
`ALTER TABLE' creates a temporary table in the same directory as the
original table.