• MySQL知识库 :: performance
  • Excessive Disk Temporary Table Usage Detected

  • Description

    To improve performance, MySQL tries to use MEMORY table (i.e., a temporary table) when SQL statements contain GROUP BY or JOIN statements. Most temporary tables are memory-based hash tables. Temporary tables with a large row length (calculated as the sum of all column lengths) or that contain BLOB or TEXT columns, are stored on disk. If an internal heap table exceeds the size given in the tmp_table_size or max_heap_table_size variables, MySQL will handle this automatically by changing the in-memory HEAP table to a disk-based MyISAM table as necessary.

    Best Practice

    It's ideal to have most temporary tables created in memory, leaving exceedingly large temporary tables to be created on disk. A MEMORY table can be accessed faster than a disk-based table. If the size of the tables have increased or new SQL statements have been issued, increase the size of temporary tables or memory tables. Edit the options file (i.e., my.cnf or my.ini, depending on your system) and increase the value of the tmp_table_size and max_heap_table_size variables. Make sure you have enough memory on your system because the memory for the handling of the temporary tables is allocated in the fly. When finished, restart the server.