techarticles

Optimize MySQL tmp_table_size

MySQL tmp_table_size is the maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. The on-disk table is expensive and affects your database performance. The temporary table may be created when you do many advanced GROUP BY queries, multiple table joining without proper indexing. The default size is enough for most of the cases. However, to find out the tuned value follow the steps:
1. Find out current value of tmp_table_size

It is to be mentioned that the tmp_table_size and max_heap_table_size should be the same value. The overall goal to optimize MySQL tmp_table_size should be to prevent temp table creation as much as possible. Simply increasing tmp_table_size and max_heap_table_size lets inefficient queries and tables that lack proper indexing run amok. If percentage of tables created on disk exceeds 50% then first check your database is indexed properly specially for joining and group by columns. After tuning index, observe some days and adjust tmp_table_size.