Large Temporary Tables on Disk With MySQL Server (Doc ID 1381165.1)

Last updated on JULY 08, 2022

Applies to:

Symptoms

• MySQL uses a lot of temporary disk space.

• Large temporary tables can be seen in the MySQL temp directory : tmpdir. The size of temporary tables can be much larger than the original table : several gigabytes.

• I/O operations are slowed down due to massive writes in the temporary directory.

This kind of error can also occur when the temporary space fills up and there is no free space :

ERROR 126 (HY000): Incorrect key file for table 'C:\Users\\AppData\Local\T emp\#sqlc28_619_10.MYI'; try to repair it

Please note that the # prefix indicates a temporary table and MYI a MyISAM index file.

See also Incorrect key file for table '%s'; try to repair it

Changes

The situation can occur after statistics change, especially when using InnoDB.
Previous fast queries can suddenly use internal temporary space while they were not due to a change in explain plan.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.