optimizer_search_depth

The optimizer_search_depth variable defines the maximum depth of search performed by the optimizer for possible query execution plans.

Releem automatically tunes optimizer_search_depth and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.

The optimizer_search_depth is a system variable that is used to control the depth of search the query optimizer performs while trying to choose the best execution plan for a given SQL query. Essentially, it is a configuration setting that influences how MySQL's query planner goes about optimizing your queries.

The value of this variable defines the maximum number of permitted tables in the join order permutations that the optimizer will consider. The optimizer employs a cost-based approach, examining different join orders to identify the most efficient one. When the optimizer_search_depth value is set, it constrains the depth of this search.

The variable takes a value between 0 and 62.

• If set to 0, MySQL automatically determines the optimal search depth based on the particulars of the query, which is generally the recommended setting for most situations.
• If set to a specific positive integer, the optimizer will only consider that many number of permutations, thereby limiting its search depth.

The optimizer_search_depth variable can be configured using the command line or set at startup using the configuration file:

Command Line Configuration:
mysqld> set global optimizer_search_depth = XX

Replace XX with value to suit your database needs. To verify that the variable has been changed:

mysqld> show global variables like ‘optimizer_search_depth’

Configuration File:
[mysqld]
optimizer_search_depth variable = XX

Replace XX with value to suit your database needs. Must restart MySQL server to see changes applied.

When setting the `optimizer_search_depth` variable in MySQL, there are several considerations to keep in mind:

• System Resource Availability

Increasing the `optimizer_search_depth` value will require more computational resources, particularly memory, as MySQL needs to store and evaluate more potential query execution plans. If your system is already running close to its resource limits, increasing this variable could lead to system instability or slowdowns.

• Query Complexity

The complexity of your typical SQL queries should inform your choice of optimizer_search_depth. For simple queries involving only a few tables, a higher optimizer_search_depth value might be beneficial, as it allows the optimizer to explore a greater number of permutations.

However, for complex queries involving many tables, the additional time spent on optimization might outweigh the performance benefit gained from a more optimized query execution plan. In such cases, a lower optimizer_search_depth value or allowing MySQL to automatically determine the optimal depth (by setting it to 0) might be more appropriate.

• Balancing Optimization Time and Execution Time

The optimizer_search_depth variable essentially controls the trade-off between optimization time (how long MySQL spends determining the most efficient query execution plan) and execution time (how long the query takes to run once an execution plan has been chosen).

A higher value could lead to more efficient execution plans, reducing execution time, but at the cost of increased optimization time. Finding the right balance for your specific use case is crucial.

• Recommended Value

The default value is 62, but setting it this high is likely to be counterproductive in most cases, due to the extreme computational demand. The automatic value for optimizer_search_depth is 0, which allows MySQL to automatically choose the optimal search depth based on the specifics of each query.