optimizer_search_depth

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.

See also: System Variables for MariaDB Enterprise Server 10.6, in 10.6 CS, in 10.5 ES, in 10.5 CS, in 10.4 ES, in 10.4 CS, in 10.3 ES, in 10.3 CS, in 10.2 ES, and in 10.2 CS

USAGE

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.

PARAMETERS

Command-line

--optimizer_search_depth=#

Configuration file

Supported

Dynamic

Yes

Scope

Global, Session

Data Type

BIGINT UNSIGNED

Minimum Value

0

Maximum Value

62

Default Value

62

SKYSQL

Cloud

SkySQL Topology

ES Ver

Default

AWS

HA (Primary/Replica)

10.4

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

AWS

HA (Primary/Replica)

10.5

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

AWS

Transactional Standalone

10.4

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

AWS

Transactional Standalone

10.5

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

AWS

Distributed Transactions

10.5

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

GCP

ColumnStore

10.5

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

GCP

ColumnStore Multi

10.5

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

GCP

Galera

10.4

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

GCP

Galera

10.5

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

GCP

HA (Primary/Replica)

10.4

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

GCP

HA (Primary/Replica)

10.5

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

GCP

HTAP

10.5

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

GCP

Transactional Standalone

10.4

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

GCP

Transactional Standalone

10.5

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

GCP

Distributed Transactions

10.5

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

See MariaDB SkySQL documentation to learn more about the MariaDB SkySQL database as a service.

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'

CHANGE HISTORY

Release Series

History

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.

EXTERNAL REFERENCES