Large Temporary Tables on Disk With MySQL Server
Last updated on AUGUST 21, 2016
Applies to:MySQL Server - Version 4.0 to 5.6 [Release 4.0 to 5.6]
Information in this document applies to any platform.
- 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
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms