18.5 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 and OPTIMIZE TABLE create a temporary table in the same directory as the original table.