optimizer_search_depth

Overview

Maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to compile a query. Values smaller than the number of tables in a relation result in faster optimization, but may produce very bad query plans. If set to 0, the system will automatically pick a reasonable value.

USAGE

The optimizer_search_depth system variable can have its default value configured via SkySQL Configuration Manager.

The optimizer_search_depth system variable can be set in a configuration file:

[mariadb]
optimizer_search_depth=0

The global value of the optimizer_search_depth system variable can also be set dynamically at runtime by executing SET GLOBAL:

SET GLOBAL optimizer_search_depth=0;

The session value of the optimizer_search_depth system variable can also be set dynamically at runtime by executing SET SESSION:

SET SESSION optimizer_search_depth=0;

When the optimizer_search_depth system variable is set dynamically at runtime, its value will be reset the next time the server restarts. To make the value persist on restart, set it in a configuration file too.

DETAILS

The optimizer_search_depth system variable sets the maximum depth that the optimizer will search when determining an optimal query plan:

  • When it is set to higher values, the optimizer can determine better query plans when executing queries with a lot of joins. However, this can also cause the optimizer to take too long to determine the query plan, so the trade-off is not always worthwhile.

  • When it is set to lower values, the optimizer can spend less time determining a query plan, but the resulting query plan may not be the most optimal.

  • When it is set to 0, the optimizer tries to pick a reasonable search depth. Currently, it does this by setting the search depth to the number of tables used in the query, up to a maximum value of 7.

SYNONYMS

SCHEMA

PARAMETERS

Command-line

--optimizer_search_depth=#

Configuration file

Supported

Dynamic

Yes

Scope

Global, Session

Data Type

BIGINT UNSIGNED

Minimum Value

0

Maximum Value

62

Product Default Value

62

SKYSQL

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

Multi-Node Analytics

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

Single Node Analytics

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

Replicated Transactions

10.4

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.5

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

Single Node Transactions

10.4

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.5

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

GCP

Multi-Node Analytics

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

Single Node Analytics

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

Replicated Transactions

10.4

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.5

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

Single Node Transactions

10.4

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.5

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

ColumnStore Data Warehouse

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

Enterprise Server With Replica(s)

10.4

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.5

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

Enterprise Server Single Node

10.4

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.5

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

GCP

ColumnStore Data Warehouse

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

Enterprise Server With Replica(s)

10.4

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.5

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

Enterprise Server Single Node

10.4

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.5

62 (choices: 0, 8, 16, 32, 48, 56, 62)

10.6

62 (choices: 0, 8, 16, 32, 48, 56, 62)

PRIVILEGES

The optimizer_search_depth system variable requires the SUPER privilege to dynamically set its global value at runtime.

The optimizer_search_depth system variable does not require any special privilege to dynamically set its session value at runtime.

EXAMPLES

Effect on Explain Performance

If the EXPLAIN statement takes too long for a given query, then that may mean that the optimizer has way too many join options to search. You can confirm this by setting the optimizer_search_depth system variable to a lower value (or 0), and then checking whether the EXPLAIN statement finishes more quickly for the same query.

Dynamically Resetting Global Value to Default

The global value of the optimizer_search_depth system variable can be dynamically reset to the default value by setting it to DEFAULT:

SET GLOBAL optimizer_search_depth=0;

SHOW GLOBAL VARIABLES LIKE 'optimizer_search_depth';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| optimizer_search_depth | 0     |
+------------------------+-------+
SET GLOBAL optimizer_search_depth=DEFAULT;

SHOW GLOBAL VARIABLES LIKE 'optimizer_search_depth';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| optimizer_search_depth | 62    |
+------------------------+-------+

Resetting Session Value to Global Value

The session value of the optimizer_search_depth system variable can be reset to the global value by setting it to DEFAULT:

SET SESSION optimizer_search_depth=0;

SHOW GLOBAL VARIABLES LIKE 'optimizer_search_depth';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| optimizer_search_depth | 62    |
+------------------------+-------+
SHOW SESSION VARIABLES LIKE 'optimizer_search_depth';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| optimizer_search_depth | 0     |
+------------------------+-------+
SET SESSION optimizer_search_depth=DEFAULT;

SHOW SESSION VARIABLES LIKE 'optimizer_search_depth';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| optimizer_search_depth | 62    |
+------------------------+-------+

Invalid Value for Variable

If a user tries to set the optimizer_search_depth system variable to an invalid value, then the specific behavior depends on the value of the sql_mode system variable.

If the sql_mode system variable does not contain STRICT_ALL_TABLES and a user tries to set the optimizer_search_depth system variable to an invalid value, then the operation will appear to succeed, but a warning will be raised with the ER_TRUNCATED_WRONG_VALUE error code. The warning will point out that the value was truncated, which means that the value was automatically set to the closest invalid value:

SET GLOBAL optimizer_search_depth=-1;

SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1292 | Truncated incorrect optimizer_search_depth value: '-1' |
+---------+------+--------------------------------------------------------+
SHOW GLOBAL VARIABLES LIKE 'optimizer_search_depth';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| optimizer_search_depth | 0     |
+------------------------+-------+
SET GLOBAL optimizer_search_depth=63;

SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1292 | Truncated incorrect optimizer_search_depth value: '63' |
+---------+------+--------------------------------------------------------+
SHOW GLOBAL VARIABLES LIKE 'optimizer_search_depth';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| optimizer_search_depth | 62    |
+------------------------+-------+

If the sql_mode system variable does contain STRICT_ALL_TABLES and a user tries to set the optimizer_search_depth system variable to an invalid value, then the operation will fail with the ER_WRONG_VALUE_FOR_VAR error code:

SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
SET SESSION sql_mode=CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES');

SET GLOBAL optimizer_search_depth=-1;
ERROR 1231 (42000): Variable 'optimizer_search_depth' can't be set to the value of '-1'

Invalid Type for Variable

If a user tries to set the optimizer_search_depth system variable to an invalid argument, then the operation will fail with the ER_WRONG_TYPE_FOR_VAR error code:

SET GLOBAL optimizer_search_depth='';
ERROR 1232 (42000): Incorrect argument type to variable 'optimizer_search_depth'
SET GLOBAL optimizer_search_depth='infinity';
ERROR 1232 (42000): Incorrect argument type to variable 'optimizer_search_depth'

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

11.4 Enterprise

  • Not present.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

11.4 Enterprise

  • Not present.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES