Improve query speed in the MySQL for large data

Improve query

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).

Improve query speed in the MySQL for large data was last modified: October 19th, 2010 by Pragmaapps