MySQL's hidden space is implemented through the setting of the environment variable TMPDIR. If TMPDIR is not set, MySQL will use the system's default value, usually /tmp, /var/tmp, or /usr/tmp. MySQL uses the value of the environment variable TMPDIR as the path name of the directory where temporary files are saved. If TMPDIR is not set, MySQL will use the system's default value, usually /tmp, /var/tmp, or /usr/tmp. If the file system containing the temporary file directory is too small, you can use the "-tmpdir" option to mysqld to specify a directory in the file system with sufficient space.
In MySQL 5.1, the "-tmpdir" option can be set to a list of several paths, used in a round-robin fashion. On Unix platforms, paths are separated by the colon character ":"; on Windows, NetWare, and OS/2 platforms, paths are separated by the semicolon character ";". Note that in order to distribute the load effectively, these paths should be on different physical disks rather than in different partitions of the same disk.
If the MySQL server is being used as a replication slave, "--tmpdir" should not be set to point to a directory on a memory-based file system or a directory that will be cleared when the server host is restarted. For replication slave servers, some temporary files need to be retained when the machine is restarted so that temporary tables can be copied or LOAD DATA INFILE operations can be performed. If files in the temporary files directory are lost when the server is restarted, replication will fail.
MySQL creates all temporary files implicitly. In this way, you can ensure that all temporary files will be deleted when mysqld is terminated. The disadvantage of using hidden files is that the large temporary files that occupy the file system are not visible in the same location as the temporary files directory.
When doing sorting (ORDER BY or GROUP BY), MySQL usually uses 1 or more temporary files. The maximum disk space required is determined by the following expression:
(length of what is sorted + sizeof(row pointer))
* number of matched rows
*2
The size of the "row pointer" (row pointer) is usually 4 bytes, but in the future, for large tables, this value may be increased.
For some SELECT queries, MySQL also creates temporary SQL tables. They are not hidden tables and have names of the form SQL_*.
ALTER TABLE creates a temporary table in the same directory as the original table directory.