Question

Which system variable tells how far into the rest of each incomplete plan the optimizer should look to evaluate whether it should be expanded further?

(a) optimizer_prune_level

(b) optimizer_search_depth

(c) optimizer_search

(d) optimizer_prune

I have been asked this question at a job interview.

I would like to ask this question from MySQL Query Optimizer in division Query Optimization of MySQL

Answer 1

The correct option is (b) optimizer_search_depth

For explanation: In MySQL, the optimizer_search_depth system variable tells how far into the “future” of each incomplete plan the optimizer should look to evaluate whether it should be expanded further.