Optimizer Hints

This section details special comments you can add to SQL statements to influence the query optimizer, helping you manually select better execution plans for improved performance and query tuning.

Optimizer hints are options available that affect the execution plan.

SELECT Modifiers

HIGH PRIORITY

HIGH_PRIORITY gives the statement a higher priority. If the table is locked, high priority SELECTs will be executed as soon as the lock is released, even if other statements are queued. HIGH_PRIORITY applies only if the storage engine only supports table-level locking (MyISAM, MEMORY, MERGE). See HIGH_PRIORITY and LOW_PRIORITY clauses for details.

NO_ROWID_FILTER

This hint is available from MariaDB 12.1.

/* +NO_ROWID_FILTER([table_name [index_name [ ... ] ]] ) */

Does not consider ROWID filter for the scope of the hint (all tables in the query block, specific table, and specific indexes). See ROWID_FILTER for details.

NO_SPLIT_MATERIALIZED

This hint is available from MariaDB 12.1.

When a derived table is materialized, MariaDB processes and stores the results of that derived table temporarily before joining it with other tables. The "lateral derived" optimization specifically looks for ways to optimize these types of derived tables. It does that by pushing a splitting condition down into the derived table, to limit the number of rows materialized into the derived table. The SPLIT_MATERIALIZED hint forces this behavior, while NO_SPLIT_MATERIALIZED prevents it.

NO_SPLIT_MATERIALIZED(X) disables the use of split-materialized optimization in the context of X :

SELECT
  /*+ NO_SPLIT_MATERIALIZED(CUST_TOTALS) */
  ...
FROM
  customer
  (SELECT SUM(amount), o_custkey FROM orders GROUP BY o_custkey) as CUST_TOTALS
WHERE
   customer.c_custkey= o_custkey AND
   customer.country='FI';

ROWID_FILTER

This hint is available from MariaDB 12.1.

/* +ROWID_FILTER( [table_name [index_name [ ...] ]]) */

Like NO_RANGE_OPTIMIZATION or MRR, this hint can be applied to:

• Query blocks — NO_ROWID_FILTER()

• Table — NO_ROWID_FILTER(table_name)

• Specific indexes — NO_ROWID_FILTER(table_name index1 index2 ...)

Forces the use of ROWID_FILTER for the table index it targets:

• For query blocks and tables, it enables the use of the ROWID filter, assuming it is disabled globally.

• For indexes, it forces its use, regardless of the costs. The following query forces the use of the ROWID filter made from t1.idx1 if the chosen plan allows so (that is, if the access method to t1 allows it):

SELECT /*+ ROWID_FILTER(t1 idx1) */
...

Assuming the optimizer would pick idx2 for table t1 if the hint was not used, this could result in the usage of both idx2 and idx1 if the hint is used. That might become more expensive than a full table scan, or result in a change of the join order.

Therefore, do not "blindly" use this filter, but rather make sure its use doesn't have a negative impact as described.

SPLIT_MATERIALIZED

This hint is available from MariaDB 12.1.

When a derived table is materialized, MariaDB processes and stores the results of that derived table temporarily before joining it with other tables. The "lateral derived" optimization specifically looks for ways to optimize these types of derived tables. It does that by pushing a splitting condition down into the derived table, to limit the number of rows materialized into the derived table. The SPLIT_MATERIALIZED hint forces this behavior, while NO_SPLIT_MATERIALIZED prevents it.

SPLIT_MATERIALIZED(X) enables and forces the use of split-materialized optimization in the context of X, unless it is impossible to do (for instance, because a table is not a materialized derived table).

SELECT
  /*+ SPLIT_MATERIALIZED(CUST_TOTALS) */
  ...
FROM
  customer
  (SELECT SUM(amount), o_custkey FROM orders GROUP BY o_custkey) as CUST_TOTALS
WHERE
   customer.c_custkey= o_custkey AND
   customer.country='FI';

SQL_CACHE / SQL_NO_CACHE

If the query_cache_type system variable is set to 2 or DEMAND, and the current statement is cacheable, SQL_CACHE causes the query to be cached and SQL_NO_CACHE causes the query not to be cached. For UNIONs, SQL_CACHE or SQL_NO_CACHE should be specified for the first query. See also The Query Cache for more detail and a list of the types of statements that aren't cacheable.

SQL_BUFFER_RESULT

SQL_BUFFER_RESULT forces the optimizer to use a temporary table to process the result. This is useful to free locks as soon as possible.

SQL_SMALL_RESULT / SQL_BIG_RESULT

SQL_SMALL_RESULT and SQL_BIG_RESULT tell the optimizer whether the result is very big or not. Usually, GROUP BY and DISTINCT operations are performed using a temporary table. Only if the result is very big, using a temporary table is not convenient. The optimizer automatically knows if the result is too big, but you can force the optimizer to use a temporary table with SQL_SMALL_RESULT, or avoid the temporary table using SQL_BIG_RESULT.

STRAIGHT_JOIN

STRAIGHT_JOIN applies to the JOIN queries, and tells the optimizer that the tables must be read in the order they appear in the SELECT. For const and system table this options is sometimes ignored.

SQL_CALC_FOUND_ROWS

SQL_CALC_FOUND_ROWS is only applied when using the LIMIT clause. If this option is used, MariaDB will count how many rows would match the query, without the LIMIT clause. That number can be retrieved in the next query, using FOUND_ROWS().

USE/FORCE/IGNORE INDEX

USE INDEX, FORCE INDEX and IGNORE INDEX constrain the query planning to a specific index. For further information about some of these options, see How to force query plans.

Expanded Optimizer Hints

Current

Hints are placed after the main statement verb.

UPDATE /*+ hints */ table ...;
DELETE /*+ hints */ FROM table... ;
SELECT /*+ hints */  ...

They can also appear after the SELECT keyword in any subquery:

SELECT * FROM t1 WHERE a IN (SELECT /*+ hints */ ...)

There can be one or more hints separated with space:

hints:  hint hint ...

Description

Each individual hint is hint name and arguments. In case there are no arguments, the () brackets are still present:

hint:  hint_name([arguments])

Incorrect hints produce warnings (a setting to make them errors is not implemented yet). Hints that are not ignored are kept in the query text (you can see them in SHOW PROCESSLIST, Slow Query Log, EXPLAIN EXTENDED). Hints that were incorrect and were ignored are removed from there.

Hint Hierarchy

Hints can be:

• global - they apply to whole query;

• table-level - they apply to a table;

• index-level - they apply to an index in a table.

Table-Level Hints

hint_name([table_name [table_name [,...]] )

Index-Level Hints

Index-level hints apply to indexes. Possible syntax variants are:

hint_name(table_name [index_name [, index_name] ...])

hint_name(table_name@query_block [index_name [, index_name] ...])

hint_name(@query_block  table_name [index_name [, index_name] ...])

Query Block Naming

The QB_NAME hint is used to assign a name to the query block the hint is in. The Query block is either a SELECT statement or a top-level construct of an UPDATE or DELETE statement.

SELECT /*+ QB_NAME(foo) */ select_list FROM ...

The name can then can be used

• to refer to the query block;

• to refer to a table in the query block as table_name@query_block_name.

Query block scope is the whole statement. It is invalid to use the same name for multiple query blocks. You can refer to the query block "down into subquery", "down into derived table", "up to the parent" and "to a right sibling in the UNION". You cannot refer "to a left sibling in a UNION".

Hints inside views are not supported, yet. You can neither use hints in VIEW definitions, nor control query plans inside non-merged views. (This is because QB_NAME binding are done "early", before we know that some tables are views.)

SELECT#N NAMES

Besides the given name, any query block is given a name select#n (where #n stands for a number). You can see it when running EXPLAIN EXTENDED:

Note 1003 SELECT /*+ NO_RANGE_OPTIMIZATION(t3@select#1 PRIMARY) */ ...

It is not possible to use it in the hint text:

SELECT /*+ BKA(tbl1@`select#1`) */ 1 FROM tbl1 ...;

QB_NAME in CTEs

Hints that control @name will control the first use of the CTE (common table expression).

Effect of Optimizer Hints

The optimizer can be controlled by

1. server variables - optimizer_switch, join_cache_level, and so forth;

2. old-style hints;

3. new-style hints.

Old-style hints do not overlap with server variable settings.

New-style hints are more specific than server variable settings, so they override the server variable settings.

Hints are "narrowly interpreted" and "best effort" - if a hint dictates to do something, for example:

SELECT  /*+ MRR(t1 t1_index1) */  ... FROM t1 ...

It means: When considering a query plan that involves using t1_index1 in a way that one can use MRR, use MRR. If the query planning is such that use of t1_index1 doesn't allow to use MRR, it won't be used.

The optimizer may also consider using t1_index2 and pick that over using t1_index1. In such cases, the hint is effectively ignored and no warning is given.

List of Hints

JOIN_INDEX and NO_JOIN_INDEX

An index-level hint that enables or disables the specified indexes for an access method (range, ref, etc.). Equivalent to FORCE INDEX FOR JOIN and IGNORE INDEX FOR JOIN.

GROUP_INDEX and NO_GROUP_INDEX

An index-level hint that enables or disables the specified indexes for index scans for GROUP BY operations. Equivalent to FORCE INDEX FOR GROUP BY and IGNORE INDEX FOR GROUP BY.

ORDER_INDEX and NO_ORDER_INDEX

An index-level hint that enables or disables the specified indexes for sorting rows. Equivalent to FORCE INDEX FOR ORDER BY and IGNORE INDEX FOR ORDER BY.

INDEX and NO_INDEX

An index-level hint that enables or disables the specified indexes, for all scopes (join access method, GROUP BY, or sorting). Equivalent to FORCE INDEX and IGNORE INDEX.

NO_RANGE_OPTIMIZATION

An index-level hint that disables range optimization for certain index(es):

SELECT /*+ NO_RANGE_OPTIMIZATION(tbl index1 index2) */  * FROM tbl ...

NO_ICP

An index-level hint that disables Index Condition Pushdown for the indexes. ICP+BKA is disabled as well.

SELECT /*+ NO_ICP(tbl index1 index2) */  * FROM tbl ...

MRR and NO_MRR

Index-level hints to force or disable use of MRR.

SELECT /*+ MRR(tbl index1 index2) */  * FROM tbl ... 

SELECT /*+ NO_MRR(tbl index1 index2) */  * FROM tbl ...

This controls:

• MRR optimization for range access;

• BKA.

BKA() and NO_BKA()

Query block or table-level hints.

BKA() also enables MRR to make BKA possible. (This is different from session variables, where you need to enable MRR separately). This also enables BKAH.

BNL() and NO_BNL()

Controls BNL-H.

The implementation is "BNL() hint effectively increases join_cache_level up to 4 " .. for the table(s) it applies to.

MAX_EXECUTION_TIME()

Global-level hint to limit query execution time

SELECT /*+ MAX_EXECUTION_TIME(milliseconds) */ ...  ;

A query that doesn't finish in the time specified will be aborted with an error.

If @@max_statement_time is set, the hint will be ignored and a warning produced. Note that this contradicts the stated principle that "new-style hints are more specific than server variable settings, so they override the server variable settings".

SPLIT_MATERIALIZED(X) and NO_SPLIT_MATERIALIZED(X)

Enables or disables the use of the Split Materialized Optimization (also called the Lateral Derived Optimization).

DERIVED_CONDITION_PUSHDOWN and NO_DERIVED_CONDITION_PUSHDOWN

Enables or disables the use of condition pushdown for derived tables.

MERGE and NO_MERGE

Table-level hint that enables the use of merging, or disables and uses materialization, for the specified tables, views or common table expressions.

SUBQUERY Hint

Query block-level hint.

SUBQUERY([@query_block_name] MATERIALIZATION)

SUBQUERY([@query_block_name] INTOEXISTS)

This controls non-semi-join subqueries. The parameter specifies which subquery to use. Use of this hint disables conversion of subquery into semi-join.

For details, see the Subquery Hints section.

SEMIJOIN and NO_SEMIJOIN

Query block-level hints.

This controls the conversion of subqueries to semi-joins and which semi-join strategies are allowed.

[NO_]SEMIJOIN([@query_block_name] [strategy [, strategy] ...])

where the strategy is one of DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION.

<12.1

< 12.0

Join Order Hints

Current

Syntax

Syntax of the JOIN_FIXED_ORDER hint:

hint_name([@query_block_name])

Syntax of other join-order hints:

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

Description

The following hints are available:

• JOIN_FIXED_ORDER([@query_block_name]) Forces the optimizer to join tables using the order in which they appear in the FROM clause. This is the same as specifying SELECT STRAIGHT_JOIN.

• JOIN_ORDER([@query_block_name] tbl [, tbl] ...) Instructs the optimizer to join tables using the specified table order. The hint applies to the named tables. The optimizer may place tables that are not named anywhere in the join order, including between specified tables.

  • Alternative syntax: JOIN_ORDER(tbl[@query_block_name] [, tbl[@query_block_name]] ...)

• JOIN_PREFIX([@query_block_name] tbl [, tbl] ...) Instructs the optimizer to join tables using the specified table order for the first tables of the join execution plan. The hint applies to the named tables. The optimizer places all other tables after the named tables.

  • Alternative syntax: JOIN_PREFIX(tbl[@query_block_name] [, tbl[@query_block_name]] ...)

• JOIN_SUFFIX([@query_block_name] tbl [, tbl] ...) Instructs the optimizer to join tables using the specified table order for the last tables of the join execution plan. The hint applies to the named tables. The optimizer places all other tables before the named tables.

Notes for the list above:

• Alternative syntax: JOIN_SUFFIX(tbl[@query_block_name] [, tbl[@query_block_name]] ...)

• tbl is the name of a table used in the statement. A hint that names tables applies to all tables that it names. The JOIN_FIXED_ORDER hint names no tables and applies to all tables in the FROM clause of the query block in which it occurs.

• query_block_name is the query block to which the hint applies. If the hint includes no leading @query_block_name, it applies to the query block in which it occurs. When using the tbl@query_block_name syntax, the hint applies to the named table in the named query block. To assign a name to a query block, see Optimizer Hints for Naming Query Blocks.

General notes:

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

< 12.0

Subquery Hints

Current

Overview

Subquery hints determine:

• If semijoin transformations are to be used;

• Which semijoin strategies are permitted;

• When semijoins are not used, whether to use subquery materialization or IN-to-EXISTS transformations.

Syntax

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

• hint_name: The following hint names are permitted to enable or disable the named semijoin strategies: SEMIJOIN, NO_SEMIJOIN.

• strategy: Enable or disable a semi-join strategy. The following strategy names are permitted: DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION.

Strategies

For SEMIJOIN hints, if no strategies are named, semi-join is used based on the strategies enabled according to the optimizer_switch system variable, if possible. If strategies are named, but inapplicable for the statement, DUPSWEEDOUT is used.

For NO_SEMIJOIN hints, semi-join is not used if no strategies are named. If named strategies rule out all applicable strategies for the statement, DUPSWEEDOUT is used.

If a 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, the optimizer may generate a query plan that is far from optimal.

Examples

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

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

SUBQUERY([@query_block_name] strategy)

The hint name is always SUBQUERY.

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

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

< 12.0

See Also

• Use optimizer_switch to enable/disable specific optimizations

_{This page is licensed: CC BY-SA / Gnu FDL}