Where MariaDB Stores Temporary Files
On Unix, MariaDB uses the value of the TMPDIR environment variable as the path name of the directory in which to store temporary files. If TMPDIR is not set, MariaDB uses the system default, which is usually /tmp, /var/tmp, or /usr/tmp.
On Windows, MariaDB checks in order the values of the TMPDIR, TEMP, and TMP environment variables. For the first one found to be set, MariaDB uses it and does not check those remaining. If none of TMPDIR, TEMP, or TMP are set, MariaDB uses the Windows system default, which is usually C:\windows\temp\.
If the file system containing your temporary file directory is too small, you can use the --tmpdir option to mysqld to specify a directory in a file system where you have enough space.
In MariaDB 5.6, the --tmpdir option can be set to a list of several paths that are used in round-robin fashion. Paths should be separated by colon characters (":") on Unix and semicolon characters (";") on Windows.Note
To spread the load effectively, these paths should be located on different physical disks, not different partitions of the same disk.
If the MariaDB server is acting as a replication slave, you should not set --tmpdir to point to a directory on a memory-based file system or to a directory that is cleared when the server host restarts. A replication slave needs some of its temporary files to survive a machine restart so that it can replicate temporary tables or LOAD DATA INFILE operations. If files in the temporary file directory are lost when the server restarts, replication fails.
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 file system in which the temporary file directory is located.
When sorting (ORDER BY or GROUP BY), MariaDB normally uses one or two 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 row pointer size is usually four bytes, but may grow in the future for really big tables.
For some SELECT queries, MariaDB 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.