MySQL: Controlling Query Optimizer to choose the Best Execution Plan

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.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

Be the First to Comment!

Notify of