Optimizer Hints

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.

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.

MariaDB starting with 12.0

Expanded Optimizer Hints

Syntax

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 or a top-level construct of 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. 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

server variables - optimizer_switch, join_cache_level, and so forth;
old-style hints;
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

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 (mdev35483-mrr-is-narrow.sql);
BKA mdev35483-mrr-controls-bka-partially.sql.

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

Subquery Hints

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.

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.