I was executing select query which had 4-5 table join on large data set for report and query was taking too much time or not giving any result even after an hour even tables were proper indexed. Later on I started to find out the reason of this behavior and found that there is some problem in mysql’s configurations.
I have to change mysql configuration file( my.cnf) as:
1. tmp_table_size: from 32M to 128M (This might not work because of known bug)
2. max_heap_table_size: 32M to 128M
After this change, query is taking around 10 seconds for execution which is quite faster.
Reason:
Mysql creates temporary table for the results is an on-disk table.If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values.The max_heap_table_size system variable determines how large the table is permitted to grow and there is no conversion to on-disk format.
If the value of max_heap_table_size is small then converts in-memory table to an on-disk table.Due to this on-disk table, it would take too much time for I/O read in comparison of in-memory table(temp table).
