Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.1Mb
PDF (A4) - 35.2Mb
Man Pages (TGZ) - 256.4Kb
Man Pages (Zip) - 361.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

8.9.3 Optimizer Hints

One means of control over optimizer strategies is to set theoptimizer_switch system variable (seeSection 8.9.2, “Switchable Optimizations”). Changes to this variable affect execution of all subsequent queries; to affect one query differently from another, it is necessary to changeoptimizer_switch before each one.

another way to control the optimizer is by using optimizer hints, which can be specified within individual statements. Because optimizer hints apply on a per-statement basis, they provide finer control over statement execution plans than can be achieved usingoptimizer_switch. For example, you can enable an optimization for one table in a statement and disable the optimization for a different table. Hints within a statement take precedence overoptimizer_switch flags.

Examples:

SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1  FROM t3 WHERE f1 > 30 AND f1 < 33;SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
Note

Themysql client by default strips comments from SQL statements sent to the server (including optimizer hints) until MySQL 5.7.7, when it was changed to pass optimizer hints to the server. To ensure that optimizer hints are not stripped if you are using an older version of themysql client with a version of the server that understands optimizer hints, invokemysql with the--comments option.

Optimizer hints, described here, differ from index hints, described inSection 8.9.4, “Index Hints”. Optimizer and index hints may be used separately or together.

Optimizer Hint Overview

Optimizer hints apply at different scope levels:

  • Global: The hint affects the entire statement

  • Query block: The hint affects a particular query block within a statement

  • Table-level: The hint affects a particular table within a query block

  • Index-level: The hint affects a particular index within a table

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.

Table 8.2 Optimizer Hints Available

Hint NameDescriptionApplicable Scopes
BKA,NO_BKAAffects Batched Key Access join processingQuery block, table
BNL,NO_BNLAffects Block Nested-Loop join processingQuery block, table
MAX_EXECUTION_TIMELimits statement execution timeGlobal
MRR,NO_MRRAffects Multi-Range Read optimizationTable, index
NO_ICPAffects Index Condition Pushdown optimizationTable, index
NO_RANGE_OPTIMIZATIONAffects range optimizationTable, index
QB_NAMEAssigns name to query blockQuery block
SEMIJOIN,NO_SEMIJOINsemijoin strategiesQuery block
SUBQUERYAffects materialization,IN-to-EXISTS subquery stratgiesQuery block

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

Optimizer Hint Syntax

MySQL supports comments in SQL statements as described inSection 9.6, “Comments”. 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:

/*+ BKA(t1) *//*+ BNL(t1, t2) *//*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) *//*+ QB_NAME(qb2) */

Whitespace is permitted after the+ character.

The parser recognizes optimizer hint comments after the initial keyword ofSELECT,UPDATE,INSERT,REPLACE, andDELETE statements. Hints are permitted in these contexts:

  • At the beginning of query and data change statements:

    SELECT /*+ ... */ ...INSERT /*+ ... */ ...REPLACE /*+ ... */ ...UPDATE /*+ ... */ ...DELETE /*+ ... */ ...

  • At the beginning of query blocks:

    (SELECT /*+ ... */ ... )(SELECT ... ) UNION (SELECT /*+ ... */ ... )(SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)INSERT ... SELECT /*+ ... */ ...

  • In hintable statements prefaced byEXPLAIN. For example:

    EXPLAIN SELECT /*+ ... */ ...EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)

    The implication is that you can useEXPLAIN to see how optimizer hints affect execution plans. UseSHOW WARNINGS immediately afterEXPLAIN to see how hints are used. The extendedEXPLAIN output displayed by a followingSHOW WARNINGS indicates which hints were used. Ignored hints are not displayed.

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

SELECT /*+ BNL(t1) BKA(t2) */ ...

But this is invalid:

SELECT /*+ BNL(t1) */ /* BKA(t2) */ ...

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.

  • Duplicate hints: For a hint such as/*+ MRR(idx1) MRR(idx1) */, MySQL uses the first hint and issues a warning about the duplicate hint.

  • Conflicting hints: For a hint such as/*+ MRR(idx1) NO_MRR(idx1) */, MySQL uses the first hint and issues a warning about the second conflicting hint.

Query block names are identifiers and follow the usual rules about what names are valid and how to quote them (seeSection 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 (seeSection 9.2.3, “Identifier Case Sensitivity”).

Table-Level Optimizer Hints

Table-level hints affect use of the Block Nested-Loop (BNL) and Batched Key Access (BKA) join-processing algorithms (seeSection 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”). These hint types apply to specific tables, or all tables in a query block.

Syntax of table-level hints:

hint_name([@query_block_name] [tbl_name [,tbl_name] ...])hint_name([tbl_name@query_block_name [,tbl_name@query_block_name] ...])

The syntax refers to these terms:

  • hint_name: These hint names are permitted:

    • BKA,NO_BKA: Enable or disable BKA for the specified tables.

    • BNL,NO_BNL: Enable or disable BNL for the specified tables.

    Note

    To use a BNL or BKA hint to enable join buffering for any inner table of an outer join, join buffering must be enabled for all inner tables of the outer join.

  • tbl_name: The name of a table used in the statement. The hint applies to all tables that it names. If the hint names no tables, it applies to all tables of the query block in which it occurs.

    If a table has an alias, hints must refer to the alias, not the table name.

    Table names in hints cannot be qualified with schema names.

  • query_block_name: 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. Fortbl_name@query_block_name syntax, the hint applies to the named table in the named query block. To assign a name to a query block, seeOptimizer Hints for Naming Query Blocks.

Examples:

SELECT /*+ NO_BKA(t1, t2) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;SELECT /*+ NO_BNL() BKA(t1) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;

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

SELECT /*+ BNL(t2) */ FROM t1, t2;

If the optimizer chooses to processt1 first, it applies a Block Nested-Loop join tot2 by buffering the rows fromt1 before starting to read fromt2. If the optimizer instead chooses to processt2 first, the hint has no effect becauset2 is a sender table.

Index-Level Optimizer Hints

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), and range optimizations (seeSection 8.2.1, “Optimizing SELECT Statements”).

Syntax of index-level hints:

hint_name([@query_block_name]tbl_name [index_name [,index_name] ...])hint_name(tbl_name@query_block_name [index_name [,index_name] ...])

The syntax refers to these terms:

  • hint_name: These hint names are permitted:

    • MRR,NO_MRR: Enable or disable MRR for the specified table or indexes. MRR hints apply only toInnoDB andMyISAM tables.

    • NO_ICP: Disable ICP for the specified table or indexes. By default, ICP is a candidate optimization strategy, so there is no hint for enabling it.

    • NO_RANGE_OPTIMIZATION: Disable index range access for the specified table or indexes. This hint also disables Index Merge and Loose Index Scan for the table or indexes. By default, range access is a candidate optimization strategy, so there is no hint for enabling it.

      This hint may be useful when the number of ranges may be high and range optimization would require many resources.

  • tbl_name: The table to which the hint applies.

  • index_name: The name of an index in the named table. The hint applies to all indexes that it names. If the hint names no indexes, it applies to all indexes in the table.

    To refer to a primary key, use the namePRIMARY. To see the index names for a table, useSHOW INDEX.

  • query_block_name: 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. Fortbl_name@query_block_name syntax, the hint applies to the named table in the named query block. To assign a name to a query block, seeOptimizer Hints for Naming Query Blocks.

Examples:

SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1  FROM t3 WHERE f1 > 30 AND f1 < 33;INSERT INTO t3(f1, f2, f3)  (SELECT /*+ NO_ICP(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2   WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1   AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);

Subquery Optimizer Hints

Subquery hints affect whether to use semijoin transformations and which semijoin strategies to permit, and, when semijoins are not used, whether to use subquery materialization orIN-to-EXISTS transformations. For more information about these optimizations, seeSection 8.2.2, “Optimizing Subqueries, Derived Tables, and View References”.

Syntax of hints that affect semijoin strategies:

hint_name([@query_block_name] [strategy [,strategy] ...])

The syntax refers to these terms:

  • hint_name: These hint names are permitted:

  • strategy: A semijoin strategy to be enabled or disabled. These strategy names are permitted:DUPSWEEDOUT,FIRSTMATCH,LOOSESCAN,MATERIALIZATION.

    ForSEMIJOIN hints, if no strategies are named, semijoin is used if possible based on the strategies enabled according to theoptimizer_switch system variable. If strategies are named but inapplicable for the statement,DUPSWEEDOUT is used.

    ForNO_SEMIJOIN hints, if no strategies are named, semijoin is not used. If strategies are named that rule out all applicable strategies for the statement,DUPSWEEDOUT is used.

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

IfDUPSWEEDOUT 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 settingoptimizer_prune_level=0.

Examples:

SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

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

SUBQUERY([@query_block_name]strategy)

The hint name is alwaysSUBQUERY.

ForSUBQUERY hints, thesestrategy values are permitted:INTOEXISTS,MATERIALIZATION.

Examples:

SELECT id, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);

For semijoin andSUBQUERY 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, seeOptimizer 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.

Statement Execution Time Optimizer Hints

TheMAX_EXECUTION_TIME hint is permitted only forSELECT statements. It places a limitN (a timeout value in milliseconds) on how long a statement is permitted to execute before the server terminates it:

MAX_EXECUTION_TIME(N)

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

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

TheMAX_EXECUTION_TIME(N) hint sets a statement execution timeout ofN milliseconds. If this option is absent orN is 0, the statement timeout established by themax_execution_time system variable applies.

TheMAX_EXECUTION_TIME hint is applicable as follows:

  • For statements with multipleSELECT keywords, such as unions or statements with subqueries,MAX_EXECUTION_TIME applies to the entire statement and must appear after the firstSELECT.

  • It applies to read-onlySELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect.

  • It does not apply toSELECT statements in stored programs and is ignored.

Optimizer Hints for Naming Query Blocks

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 theQB_NAME hint, which assigns a name to the query block in which it occurs:

QB_NAME(name)

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:

SELECT ...  FROM (SELECT ...  FROM (SELECT ... FROM ...)) ...

QB_NAME hints assign names to query blocks in the statement:

SELECT /*+ QB_NAME(qb1) */ ...  FROM (SELECT /*+ QB_NAME(qb2) */ ...  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

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

SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...  FROM (SELECT /*+ QB_NAME(qb2) */ ...  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

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 (seeSection 9.2, “Schema Object Names”). For example, a query block name that contains spaces must be quoted, which can be done using backticks:

SELECT /*+ BKA(@`my hint name`) */ ...  FROM (SELECT /*+ QB_NAME(`my hint name`) */ ...) ...

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

SELECT /*+ BKA(@"my hint name") */ ...  FROM (SELECT /*+ QB_NAME("my hint name") */ ...) ...