8.9.2 Optimizer Hints

Optimizer hints apply at different scope levels:

The following table summarizes the available optimizer hints, the optimizer strategies they affect, and the scope or scopes at which they apply. More details are given later.

Disabling an optimization prevents the optimizer from using it. Enabling an optimization means the optimizer is free to use the strategy if it applies to statement execution, not that the optimizer necessarily will use it.

MySQL supports comments in SQL statements as described in Section 9.6, “Comment Syntax”. Optimizer hints must be specified within /*+ ... */ comments. That is, optimizer hints use a variant of /* ... */ C-style comment syntax, with a + character following the /* comment opening sequence. Examples:

Whitespace is permitted after the + character.

The parser recognizes optimizer hint comments after the initial keyword of SELECT, UPDATE, INSERT, REPLACE, and DELETE statements. Hints are permitted in these contexts:

A hint comment may contain multiple hints, but a query block cannot contain multiple hint comments. This is valid:

But this is invalid:

When a hint comment contains multiple hints, the possibility of duplicates and conflicts exists. The following general guidelines apply. For specific hint types, additional rules may apply, as indicated in the hint descriptions.

Query block names are identifiers and follow the usual rules about what names are valid and how to quote them (see Section 9.2, “Schema Object Names”).

Hint names, query block names, and strategy names are not case sensitive. References to table and index names follow the usual identifier case sensitivity rules (see Section 9.2.2, “Identifier Case Sensitivity”).

Join-order hints affect the order in which the optimizer joins tables.

Syntax of the JOIN_FIXED_ORDER hint:

Syntax of other join-order hints:

The syntax refers to these terms:

Example:

Hints control the behavior of semi-join tables that are merged to the outer query block. If subqueries subq1 and subq2 are converted to semi-joins, tables t4@subq1 and t5@subq2 are merged to the outer query block. In this case, the hint specified in the outer query block controls the behavior of t4@subq1, t5@subq2 tables.

The optimizer resolves join-order hints according to these principles:

Table-level hints affect:

These hint types apply to specific tables, or all tables in a query block.

Syntax of table-level hints:

The syntax refers to these terms:

Examples:

A table-level hint applies to tables that receive records from previous tables, not sender tables. Consider this statement:

If the optimizer chooses to process t1 first, it applies a Block Nested-Loop join to t2 by buffering the rows from t1 before starting to read from t2. If the optimizer instead chooses to process t2 first, the hint has no effect because t2 is a sender table.

For the MERGE and NO_MERGE hints, these precedence rules apply:

Index-level hints affect which index-processing strategies the optimizer uses for particular tables or indexes. These hint types affect use of Index Condition Pushdown (ICP), Multi-Range Read (MRR), Index Merge, and range optimizations (see Section 8.2.1, “Optimizing SELECT Statements”).

Syntax of index-level hints:

The syntax refers to these terms:

Examples:

The following examples use the Index Merge hints, but other index-level hints follow the same principles regarding hint ignoring and precedence of optimizer hints in relation to the optimizer_switch system variable or index hints.

Assume that table t1 has columns a, b, c, and d; and that indexes named i_a, i_b, and i_c exist on a, b, and c, respectively:

Index Merge is used for (i_a, i_b, i_c) in this case.

Index Merge is used for (i_b, i_c) in this case.

NO_INDEX_MERGE is ignored because there is a preceding hint for the same table.

INDEX_MERGE is ignored because there is a preceding hint for the same table.

For the INDEX_MERGE and NO_INDEX_MERGE optimizer hints, these precedence rules apply:

Subquery hints affect whether to use semi-join transformations and which semi-join strategies to permit, and, when semi-joins are not used, whether to use subquery materialization or IN-to-EXISTS transformations. For more information about these optimizations, see Section 8.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”.

Syntax of hints that affect semi-join strategies:

The syntax refers to these terms:

If one subquery is nested within another and both are merged into a semi-join of an outer query, any specification of semi-join strategies for the innermost query are ignored. SEMIJOIN and NO_SEMIJOIN hints can still be used to enable or disable semi-join transformations for such nested subqueries.

If DUPSWEEDOUT is disabled, on occasion the optimizer may generate a query plan that is far from optimal. This occurs due to heuristic pruning during greedy search, which can be avoided by setting optimizer_prune_level=0.

Examples:

Syntax of hints that affect whether to use subquery materialization or IN-to-EXISTS transformations:

The hint name is always SUBQUERY.

For SUBQUERY hints, these strategy values are permitted: INTOEXISTS, MATERIALIZATION.

Examples:

For semi-join and SUBQUERY hints, a leading @query_block_name specifies the query block to which the hint applies. If the hint includes no leading @query_block_name, the hint applies to the query block in which it occurs. To assign a name to a query block, see Optimizer Hints for Naming Query Blocks.

If a hint comment contains multiple subquery hints, the first is used. If there are other following hints of that type, they produce a warning. Following hints of other types are silently ignored.

The MAX_EXECUTION_TIME hint is permitted only for SELECT statements. It places a limit N (a timeout value in milliseconds) on how long a statement is permitted to execute before the server terminates it:

Example with a timeout of 1 second (1000 milliseconds):

The MAX_EXECUTION_TIME(N) hint sets a statement execution timeout of N milliseconds. If this option is absent or N is 0, the statement timeout established by the max_execution_time system variable applies.

The MAX_EXECUTION_TIME hint is applicable as follows:

The SET_VAR hint sets the session value of a system variable temporarily (for the duration of a single statement). Examples:

Syntax of the SET_VAR hint:

var_name names a system variable that has a session value (although not all such variables can be named, as explained later). value is the value to assign to the variable; the value must be a scalar.

SET_VAR makes a temporary variable change, as demonstrated by these statements:

With SET_VAR, there is no need to save and restore the variable value. This enables you to replace multiple statements by a single statement. Consider this sequence of statements:

The sequence can be replaced by this single statement:

Standalone SET statements permit any of these syntaxes for naming session variables:

Because the SET_VAR hint applies only to session variables, session scope is implicit, and SESSION, @@SESSION., and @@ are neither needed nor permitted. Including explicit session-indicator syntax results in the SET_VAR hint being ignored with a warning.

Not all session variables are permitted for use with SET_VAR. Individual system variable descriptions indicate whether each variable is hintable; see Section 5.1.8, “Server System Variables”. You can also check a system variable at runtime by attempting to use it with SET_VAR. If the variable is not hintable, a warning occurs:

SET_VAR syntax permits setting only a single variable, but multiple hints can be given to set multiple variables:

If several hints with the same variable name appear in the same statement, the first one is applied and the others are ignored with a warning:

In this case, the second hint is ignored with a warning that it is conflicting.

A SET_VAR hint is ignored with a warning if no system variable has the specified name or the variable value is incorrect:

For the first statement, there is no max_size variable. For the second statement, mrr_cost_flag takes values of on or off, so attempting to set it to yes is incorrect. In each case, the hint is ignored with a warning.

The SET_VAR hint is permitted only at the statement level. If used in a subquery, the hint is ignored with a warning.

Slave servers ignore SET_VAR hints in replicated statements to avoid the potential for security issues.

The RESOURCE_GROUP optimizer hint is used for resource group management (see Section 8.12.5, “Resource Groups”). This hint assigns the thread that executes a statement to the named resource group temporarily (for the duration of the statement). It requires the RESOURCE_GROUP_ADMIN or RESOURCE_GROUP_USER privilege.

Examples:

Syntax of the RESOURCE_GROUP hint:

group_name indicates the resource group to which the thread should be assigned for the duration of statement execution. If the group is nonexistent, a warning occurs and the hint is ignored.

The RESOURCE_GROUP hint must appear after the initial statement keyword (SELECT, INSERT, REPLACE, UPDATE, or DELETE).

An alternative to RESOURCE_GROUP is the SET RESOURCE GROUP statement, which nontemporarily assigns threads to a resource group. See Section 13.7.2.4, “SET RESOURCE GROUP Syntax”.

Table-level, index-level, and subquery optimizer hints permit specific query blocks to be named as part of their argument syntax. To create these names, use the QB_NAME hint, which assigns a name to the query block in which it occurs:

QB_NAME hints can be used to make explicit in a clear way which query blocks other hints apply to. They also permit all non-query block name hints to be specified within a single hint comment for easier understanding of complex statements. Consider the following statement:

QB_NAME hints assign names to query blocks in the statement:

Then other hints can use those names to refer to the appropriate query blocks:

The resulting effect is as follows:

Query block names are identifiers and follow the usual rules about what names are valid and how to quote them (see Section 9.2, “Schema Object Names”). For example, a query block name that contains spaces must be quoted, which can be done using backticks:

If the ANSI_QUOTES SQL mode is enabled, it is also possible to quote query block names within double quotation marks: