Tweaking MySQL Optimizer for performance

We can directly control MySQL optimizer with two popular system variables, They are optimizer_prune_level and optimizer_search_depth . You must be very careful while changing these parameters in production, If not tested well in staging before there are high chances of experiencing undesired results and performance degradation. 
The optimizer_prune_level variable influence optimizer in skipping the selected execution plans based on total number of records accessed for each table. The default value is "1" and this works well in most of cases but if you strongly believe (with supporting evidence is always better) MySQL optimizer is missing much better execution plan then disable optimizer_prune_level to "0". In many cases even after disabling optimizer_prune_level MySQL end-up using plans recommended by optimizer
By setting optimizer_search_depth MySQL system variable you are telling to optimizer how far into the "future" of each incomplete execution plan it should evaluate to expand further. In simple terms maximum depth of search performed by the query optimizer., The default value is "62" (which is also the highest configurable value), The values larger than the number of relations in a query result in better query plans, but take longer to generate an execution plan for a query. The values smaller than the number of relations in a query return an execution plan quicker, but the resulting plan may be far from being optimal. If set to 0, the system automatically picks a reasonable value.

No comments: