optimizer_search_depth

This page is part of MariaDB's Documentation.

The parent of this page is: System Variables for MariaDB Enterprise Server

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)

See SkySQL details in MariaDB SkySQL previous release
See SkySQL details in MariaDB SkySQL new release

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
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
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.