MySQL provides optimizer control through system variables that affect how query plans are evaluated, switchable optimizations, optimizer and index hints, and the optimizer cost model.
The server maintains histogram【ˈhɪstəɡræm 直方圖;（統計學的）直方圖，矩形圖;】 statistics about column values in the column_statistics data dictionary table.Like other data dictionary tables, this table is not directly accessible by users. Instead, you can obtain histogram information by querying INFORMATION_SCHEMA.COLUMN_STATISTICS, which is implemented as a view on the data dictionary table. You can also perform histogram management using the ANALYZE TABLE statement.
1 Controlling Query Plan Evaluation
The task of the query optimizer is to find an optimal【ˈɑːptɪməl 最佳的;最優的;】 plan for executing an SQL query. Because the difference in performance between “good” and “bad” plans can be orders of magnitude【ˈmæɡnɪtuːd 巨大;震級;星等;重大;重要性;星的亮度;】 (that is, seconds versus【ˈvɜːrsəs (表示兩隊或雙方對陣)對，訴，對抗;(比較兩種不同想法、選擇等)與…相對，與…相比;】 hours or even days), most query optimizers, including that of MySQL, perform a more or less exhaustive【ɪɡˈzɔːstɪv 詳盡的;全面的;徹底的;】 search for an optimal plan among all possible query evaluation plans. For join queries, the number of possible plans investigated by the MySQL optimizer grows exponentially【ˌɛkspoʊˈnɛnʃəli 以指數方式;】 with the number of tables referenced in a query. For small numbers of tables (typically less than 7 to 10) this is not a problem. However, when larger queries are submitted, the time spent in query optimization may easily become the major bottleneck in the server's performance.
A more flexible【ˈfleksəbl 靈活的;柔韌的;有彈性的;可彎曲的;可變動的;能適應新情況的;】 method for query optimization enables the user to control how exhaustive【ɪɡˈzɔːstɪv 詳盡的;全面的;徹底的;】 the optimizer is in its search for an optimal query evaluation plan. The general idea is that the fewer plans that are investigated by the optimizer, the less time it spends in compiling a query. On the other hand, because the optimizer skips some plans, it may miss finding an optimal plan.
The behavior of the optimizer with respect to the number of plans it evaluates can be controlled using two system variables:
• The optimizer_prune_level variable tells the optimizer to skip certain plans based on estimates of the number of rows accessed for each table. Our experience shows that this kind of “educated guess” rarely misses optimal plans, and may dramatically【drə'mætɪkli 顯著地;戲劇性地;戲劇地;】 reduce query compilation【ˌkɑːmpɪˈleɪʃn 彙編;編寫;收集;編纂;編著;選編;選輯;】 times. That is why this option is on (optimizer_prune_level=1) by default. However, if you believe that the optimizer missed a better query plan, this option can be switched off (optimizer_prune_level=0) with the risk that query compilation may take much longer. Note that, even with the use of this heuristic, the optimizer still explores a roughly exponential number of plans.
• The optimizer_search_depth variable tells how far into the “future” of each incomplete plan the optimizer should look to evaluate whether it should be expanded further. Smaller values of optimizer_search_depth may result in orders of magnitude【ˈmæɡnɪtuːd 巨大;震級;星等;重大;重要性;星的亮度;】 smaller query compilation times. For example, queries with 12, 13, or more tables may easily require hours and even days to compile if optimizer_search_depth is close to the number of tables in the query. At the same time, if compiled with optimizer_search_depth equal to 3 or 4, the optimizer may compile in less than a minute for the same query. If you are unsure of what a reasonable value is for optimizer_search_depth, this variable can be set to 0 to tell the optimizer to determine the value automatically.
2 Switchable Optimizations
The optimizer_switch system variable enables control over optimizer behavior. Its value is a set of flags, each of which has a value of on or off to indicate whether the corresponding optimizer behavior is enabled or disabled. This variable has global and session values and can be changed at runtime. The global default can be set at server startup.
To see the current set of optimizer flags, select the variable value:
mysql> SELECT @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on, index_merge_sort_union=on,index_merge_intersection=on, engine_condition_pushdown=on,index_condition_pushdown=on, mrr=on,mrr_cost_based=on,block_nested_loop=on, batched_key_access=off,materialization=on,semijoin=on, loosescan=on,firstmatch=on,duplicateweedout=on, subquery_materialization_cost_based=on, use_index_extensions=on,condition_fanout_filter=on, derived_merge=on,use_invisible_indexes=off,skip_scan=on, hash_join=on,subquery_to_derived=off, prefer_ordering_index=on,hypergraph_optimizer=off, derived_condition_pushdown=on 1 row in set (0.00 sec)
To change the value of optimizer_switch, assign a value consisting of a comma-separated list of one or more commands:
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';
Each command value should have one of the forms shown in the following table.
The order of the commands in the value does not matter, although the default command is executed first if present. Setting an opt_name flag to default sets it to whichever of on or off is its default value. Specifying any given opt_name more than once in the value is not permitted and causes an error. Any errors in the value cause the assignment to fail with an error, leaving the value of optimizer_switch unchanged.
The following list describes the permissible opt_name flag names, grouped by optimization strategy:
• Batched Key Access Flags
• batched_key_access (default off)
Controls use of BKA join algorithm.
For batched_key_access to have any effect when set to on, the mrr flag must also be on. Currently, the cost estimation for MRR is too pessimistic. Hence, it is also necessary for mrr_cost_based to be off for BKA to be used.
• Block Nested-Loop Flags
• block_nested_loop (default on)
Controls use of BNL join algorithm. In MySQL 8.0.18 and later, this also controls use of hash joins, as do the BNL and NO_BNL optimizer hints. In MySQL 8.0.20 and later, block nested loop support is removed from the MySQL server, and this flag controls the use of hash joins only, as do the referenced optimizer hints.
• Condition Filtering Flags
• condition_fanout_filter (default on)
Controls use of condition filtering.
• Derived Condition Pushdown Flags
• derived_condition_pushdown (default on)
Controls derived condition pushdown.
• Derived Table Merging Flags
• derived_merge (default on)
Controls merging of derived tables and views into outer query block.
The derived_merge flag controls whether the optimizer attempts to merge derived tables, view references, and common table expressions into the outer query block, assuming that no other rule prevents merging; for example, an ALGORITHM directive for a view takes precedence over the derived_merge setting. By default, the flag is on to enable merging.
• Engine Condition Pushdown Flags
• engine_condition_pushdown (default on)
Controls engine condition pushdown.
• Hash Join Flags
• hash_join (default on)
Controls hash joins in MySQL 8.0.18 only, and has no effect in any subsequent version. In MySQL 8.0.19 and later, to control hash join usage, use the block_nested_loop flag, instead.
• Index Condition Pushdown Flags
• index_condition_pushdown (default on)
Controls index condition pushdown.
• Index Extensions Flags
• use_index_extensions (default on)
Controls use of index extensions.
• Index Merge Flags
• index_merge (default on)
Controls all Index Merge optimizations.
• index_merge_intersection (default on)
Controls the Index Merge Intersection Access optimization.
• index_merge_sort_union (default on)
Controls the Index Merge Sort-Union Access optimization.
• index_merge_union (default on)
Controls the Index Merge Union Access optimization.
• Index Visibility Flags
• use_invisible_indexes (default off)
Controls use of invisible【ɪnˈvɪzəbl 看不見的;無形的(與服務而非商品有關);隱形的;】 indexes.
• Limit Optimization Flags
• prefer_ordering_index (default on)
Controls whether, in the case of a query having an ORDER BY or GROUP BY with a LIMIT clause, the optimizer tries to use an ordered index instead of an unordered index, a filesort, or some other optimization. This optimization is performed by default whenever the optimizer determines that using it would allow for faster execution of the query.
Because the algorithm that makes this determination cannot handle every conceivable【kənˈsiːvəbl 可以想象的;可想象的;可信的;】 case (due in part to the assumption【əˈsʌmpʃn 假設;假定;擔任;(責任的)承擔;(權力的)獲得;】 that the distribution of data is always more or less uniform), there are cases in which this optimization may not be desirable. Prior to MySQL 8.0.21, it was not possible to disable this optimization, but in MySQL 8.0.21 and later, while it remains the default behavior, it can be disabled by setting the prefer_ordering_index flag to off.
• Multi-Range Read Flags
• mrr (default on)
Controls the Multi-Range Read strategy.
• mrr_cost_based (default on)
Controls use of cost-based MRR if mrr=on.
• Semijoin Flags
• duplicateweedout (default on)
Controls the semijoin Duplicate Weedout strategy.
• firstmatch (default on)
Controls the semijoin FirstMatch strategy.
• loosescan (default on)
Controls the semijoin LooseScan strategy (not to be confused with Loose Index Scan for GROUP BY).
• semijoin (default on)
Controls all semijoin strategies.
In MySQL 8.0.17 and later, this also applies to the antijoin optimization.
The semijoin, firstmatch, loosescan, and duplicateweedout flags enable control over semijoin strategies. The semijoin flag controls whether semijoins are used. If it is set to on, the firstmatch and loosescan flags enable finer control over the permitted semijoin strategies.
If the duplicateweedout semijoin strategy is disabled, it is not used unless all other applicable strategies are also disabled.
If semijoin and materialization are both on, semijoins also use materialization where applicable. These flags are on by default.
• Skip Scan Flags
• skip_scan (default on)
Controls use of Skip Scan access method.
• Subquery Materialization Flags
• materialization (default on)
Controls materialization (including semijoin materialization).
• subquery_materialization_cost_based (default on)
Use cost-based materialization choice.
The materialization flag controls whether subquery materialization is used. If semijoin and materialization are both on, semijoins also use materialization where applicable. These flags are on by default.
The subquery_materialization_cost_based flag enables control over the choice between subquery materialization and IN-to-EXISTS subquery transformation. If the flag is on (the default), the optimizer performs a cost-based choice between subquery materialization and IN-to-EXISTS subquery transformation if either method could be used. If the flag is off, the optimizer chooses subquery materialization over IN-to-EXISTS subquery transformation.
• Subquery Transformation Flags
• subquery_to_derived (default off)
Beginning with MySQL 8.0.21, the optimizer is able in many cases to transform a scalar【ˈskeɪlər 標量的;純量的;無向量的;】 subquery in a SELECT, WHERE, JOIN, or HAVING clause into a left outer joins on a derived table. (Depending on the nullability of the derived table, this can sometimes be simplified further to an inner join.) This can be done for a subquery which meets the following conditions:
- The subquery does not make use of any nondeterministic functions, such as RAND().
- The subquery is not an ANY or ALL subquery which can be rewritten to use MIN() or MAX().
- The parent query does not set a user variable, since rewriting it may affect the order of execution, which could lead to unexpected results if the variable is accessed more than once in the same query.
- The subquery should not be correlated, that is, it should not reference a column from a table in the outer query, or contain an aggregate that is evaluated in the outer query.
Prior to MySQL 8.0.22, the subquery could not contain a GROUP BY clause.
This optimization can also be applied to a table subquery which is the argument to IN, NOT IN, EXISTS, or NOT EXISTS, that does not contain a GROUP BY.
The default value for this flag is off, since, in most cases, enabling this optimization does not produce any noticeable improvement in performance (and in many cases can even make queries run more slowly), but you can enable the optimization by setting the subquery_to_derived flag to on. It is primarily intended for use in testing.
Example, using a scalar subquery:
mysql> CREATE TABLE t1(a INT); mysql> CREATE TABLE t2(a INT); mysql> INSERT INTO t1 VALUES ROW(1), ROW(2), ROW(3), ROW(4); mysql> INSERT INTO t2 VALUES ROW(1), ROW(2); mysql> SELECT * FROM t1 -> WHERE t1.a > (SELECT COUNT(a) FROM t2); +------+ | a | +------+ | 3 | | 4 | +------+ mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%'; +-----------------------------------------------------+ | @@optimizer_switch LIKE '%subquery_to_derived=off%' | +-----------------------------------------------------+ | 1 | +-----------------------------------------------------+ mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 33.33 Extra: Using where *************************** 2. row *************************** id: 2 select_type: SUBQUERY table: t2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: NULL mysql> SET @@optimizer_switch='subquery_to_derived=on'; mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%'; +-----------------------------------------------------+ | @@optimizer_switch LIKE '%subquery_to_derived=off%' | +-----------------------------------------------------+ | 0 | +-----------------------------------------------------+ mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=on%'; +----------------------------------------------------+ | @@optimizer_switch LIKE '%subquery_to_derived=on%' | +----------------------------------------------------+ | 1 | +----------------------------------------------------+
mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: PRIMARY table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 33.33 Extra: Using where; Using join buffer (hash join) *************************** 3. row *************************** id: 2 select_type: DERIVED table: t2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: NULL
As can be seen from executing SHOW WARNINGS immediately following the second EXPLAIN statement, with the optimization enabled, the query SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2) is rewritten in a form similar to what is shown here:
SELECT t1.a FROM t1 JOIN ( SELECT COUNT(t2.a) AS c FROM t2 ) AS d WHERE t1.a > d.c;
Example, using a query with IN (subquery):
mysql> DROP TABLE IF EXISTS t1, t2; mysql> CREATE TABLE t1 (a INT, b INT); mysql> CREATE TABLE t2 (a INT, b INT); mysql> INSERT INTO t1 VALUES ROW(1,10), ROW(2,20), ROW(3,30); mysql> INSERT INTO t2 -> VALUES ROW(1,10), ROW(2,20), ROW(3,30), ROW(1,110), ROW(2,120), ROW(3,130); mysql> SELECT * FROM t1 -> WHERE t1.b < 0 -> OR -> t1.a IN (SELECT t2.a + 1 FROM t2); +------+------+ | a | b | +------+------+ | 2 | 20 | | 3 | 30 | +------+------+ mysql> SET @@optimizer_switch="subquery_to_derived=off"; mysql> EXPLAIN SELECT * FROM t1 -> WHERE t1.b < 0 -> OR -> t1.a IN (SELECT t2.a + 1 FROM t2)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 filtered: 100.00 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 filtered: 100.00 Extra: Using where mysql> SET @@optimizer_switch="subquery_to_derived=on"; mysql> EXPLAIN SELECT * FROM t1 -> WHERE t1.b < 0 -> OR -> t1.a IN (SELECT t2.a + 1 FROM t2)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ref possible_keys: <auto_key0> key: <auto_key0> key_len: 9 ref: std2.t1.a rows: 2 filtered: 100.00 Extra: Using where; Using index *************************** 3. row *************************** id: 2 select_type: DERIVED table: t2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 filtered: 100.00 Extra: Using temporary
Checking and simplifying the result of SHOW WARNINGS after executing EXPLAIN on this query shows that, when the subquery_to_derived flag enabled, SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2) is rewritten in a form similar to what is shown here:
SELECT a, b FROM t1 LEFT JOIN (SELECT DISTINCT a + 1 AS e FROM t2) d ON t1.a = d.e WHERE t1.b < 0 OR d.e IS NOT NULL;
Example, using a query with EXISTS (subquery) and the same tables and data as in the previous example:
mysql> SELECT * FROM t1 -> WHERE t1.b < 0 -> OR -> EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1); +------+------+ | a | b | +------+------+ | 1 | 10 | | 2 | 20 | +------+------+ mysql> SET @@optimizer_switch="subquery_to_derived=off"; mysql> EXPLAIN SELECT * FROM t1 -> WHERE t1.b < 0 -> OR -> EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 filtered: 100.00 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 filtered: 16.67 Extra: Using where mysql> SET @@optimizer_switch="subquery_to_derived=on"; mysql> EXPLAIN SELECT * FROM t1 -> WHERE t1.b < 0 -> OR -> EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 filtered: 100.00 Extra: Using where; Using join buffer (hash join) *************************** 3. row *************************** id: 2 select_type: DERIVED table: t2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 filtered: 100.00 Extra: Using temporary
If we execute SHOW WARNINGS after running EXPLAIN on the query SELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1) when subquery_to_derived has been enabled, and simplify the second row of the result, we see that it has been rewritten in a form which resembles【rɪˈzemblz 像;看起來像;顯得像;】 this:
SELECT a, b FROM t1 LEFT JOIN (SELECT DISTINCT 1 AS e1, t2.a AS e2 FROM t2) d ON t1.a + 1 = d.e2 WHERE t1.b < 0 OR d.e1 IS NOT NULL;
When you assign a value to optimizer_switch, flags that are not mentioned keep their current values. This makes it possible to enable or disable specific optimizer behaviors in a single statement without affecting other behaviors. The statement does not depend on what other optimizer flags exist and what their values are. Suppose that all Index Merge optimizations are enabled:
mysql> SELECT @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on, index_merge_sort_union=on,index_merge_intersection=on, engine_condition_pushdown=on,index_condition_pushdown=on, mrr=on,mrr_cost_based=on,block_nested_loop=on, batched_key_access=off,materialization=on,semijoin=on, loosescan=on, firstmatch=on, subquery_materialization_cost_based=on, use_index_extensions=on,condition_fanout_filter=on, derived_merge=on,use_invisible_indexes=off,skip_scan=on, hash_join=on,subquery_to_derived=off, prefer_ordering_index=on
If the server is using the Index Merge Union or Index Merge Sort-Union access methods for certain queries and you want to check whether the optimizer can perform better without them, set the variable value like this:
mysql> SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off'; mysql> SELECT @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=off, index_merge_sort_union=off,index_merge_intersection=on, engine_condition_pushdown=on,index_condition_pushdown=on, mrr=on,mrr_cost_based=on,block_nested_loop=on, batched_key_access=off,materialization=on,semijoin=on, loosescan=on, firstmatch=on, subquery_materialization_cost_based=on, use_index_extensions=on,condition_fanout_filter=on, derived_merge=on,use_invisible_indexes=off,skip_scan=on, hash_join=on,subquery_to_derived=off, prefer_ordering_index=on
3 Optimizer Hints
One means of control over optimizer strategies is to set the optimizer_switch system variable.Changes to this variable affect execution of all subsequent queries; to affect one query differently from another, it is necessary to change optimizer_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【əˈtʃiːvd (憑長期努力)達到(某目標、地位、標準);完成;成功;】 using optimizer_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 over optimizer_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 ...; SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt; INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);
Optimizer and index hints may be used separately or together.
• Optimizer Hint Overview • Optimizer Hint Syntax • Join-Order Optimizer Hints • Table-Level Optimizer Hints • Index-Level Optimizer Hints • Subquery Optimizer Hints • Statement Execution Time Optimizer Hints • Variable-Setting Hint Syntax • Resource Group Hint Syntax • Optimizer Hints for Naming Query Blocks
3.1 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.
| Hint Name | Description | Applicable Scopes |
| BKA, NO_BKA | Affects Batched Key Access join processing | Query block, table |
| BNL, NO_BNL | Prior to MySQL 8.0.20: affects Block Nested-Loop join processing; MySQL 8.0.18 and later: also affects hash join optimization; MySQL 8.0.20 and later: affects hash join optimization only | Query block, table |
|
DERIVED_CONDITION_PUSHDOWN, |
Use or ignore the derived condition pushdown optimization for materialized derived tables (Added in MySQL 8.0.22) | Query block, table |
| GROUP_INDEX, NO_GROUP_INDEX | Use or ignore the specified index or indexes for index scans in GROUP BY operations (Added in MySQL 8.0.20) | Index |
| HASH_JOIN, NO_HASH_JOIN | Affects Hash Join optimization (MySQL 8.0.18 only | Query block, table |
| INDEX, NO_INDEX | Acts as the combination of JOIN_INDEX, GROUP_INDEX, and ORDER_INDEX, or as the combination of NO_JOIN_INDEX, NO_GROUP_INDEX, and NO_ORDER_INDEX (Added in MySQL 8.0.20) | Index |
| INDEX_MERGE, NO_INDEX_MERGE | Affects Index Merge optimization | Table, index |
| JOIN_FIXED_ORDER | Use table order specified in FROM clause for join order | Query block |
| JOIN_INDEX, NO_JOIN_INDEX | Use or ignore the specified index or indexes for any access method (Added in MySQL 8.0.20) | Index |
| JOIN_ORDER | Use table order specified in hint for join order | Query block |
| JOIN_PREFIX | Use table order specified in hint for first tables of join order | Query block |
| JOIN_SUFFIX | Use table order specified in hint for last tables of join order | Query block |
| MAX_EXECUTION_TIME | Limits statement execution time | Global |
| MERGE, NO_MERGE | Affects derived table/view merging into outer query block | Table |
| MRR, NO_MRR | Affects Multi-Range Read optimization | Table, index |
| NO_ICP | Affects Index Condition Pushdown optimization | Table, index |
| NO_RANGE_OPTIMIZATION | Affects range optimization | Table, index |
| ORDER_INDEX, NO_ORDER_INDEX | Use or ignore the specified index or indexes for sorting rows (Added in MySQL 8.0.20) | Index |
| QB_NAME | Assigns name to query block | Query block |
| RESOURCE_GROUP | Set resource group during statement execution | Global |
| SEMIJOIN, NO_SEMIJOIN | Affects semijoin strategies; beginning with MySQL 8.0.17, this also applies to antijoins | Query block |
| SKIP_SCAN, NO_SKIP_SCAN | Affects Skip Scan optimization | Table, index |
| SET_VAR | Set variable during statement execution | Global |
| SUBQUERY | Affects materialization, INto-EXISTS subquery strategies | Query 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.