This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing two important parameters which might be useful to improve query execution process in MySQL Server.
The Query Optimizer is playing the main role in the Query Execution Process. It is responsible to choose best Query Execution Plan among the list of Plans.
In MySQL, we can control the task of Query Optimizer by setting few parameters.
Why because?
If you have one big SQL Query (included around 40 tables), Planner generates all possible plans mostly It generates 40+ query execution plan so now Query Optimizer will take more time to SELECT best plan.
We should tune this situation by knowing about optimizer_prune_level and optimizer_search_depth parameters.
optimizer_prune_level: Default is ON, It tells Query Optimizer to skip certain plans based on estimated number of rows.
If we have total 40 tables, Planner generates all possible 40+ plans including individual table plan which might not be needed for Query Optimizer.
If you believe that the optimizer missed a better query plan, this option can be switched off (optimizer_prune_level=0) with the risk that query compilation may take much longer.
This variable we can set both GLOBAL and Session level.
optimizer_search_depth: Default value is 62, the Planner is generating multiple plans, but sometimes It also generates an incomplete plan.
When Query Optimizer starts to scan the plan, we can set optimizer_search_depth value to tell how far each incomplete plan the optimizer should look to evaluate.
If we set a higher value (Max 63), Query Optimizer try to evaluate all incomplete plans which will take more time to execute. If we set lower value, Query Optimizer can skip few incomplete generated plans.
This variable we can set both GLOBAL and Session level.
