This is a self-contained description of Optimizer Hints in MariaDB, whatever is already implemented in the current patch.

Optimizer hints in MariaDB

General idea is to be compatible with MySQL.
MySQL hint docs: https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html

1. Hint syntax

Hints are placed after the main statement verb

UPDATE /*+ hints */ table ...;

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

SELECT /*+ hints */  ...

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

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.

2. Hint hierarchy

(todo: elaborate)
Hints can be

• global - applies to whole query
• table-level - applies to a table
• index-level - applies to an index in a table

2.1 Table-level hints

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

2.2 Index-level hints

Index-level hints apply to index(es).
Possible syntax variants:

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

3. Query block naming: QB_NAME hint

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.
One 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". One cannot refer "to a left sibling in a UNION" (It's the same in MySQL).

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

3.1 select#n names.

Besides the given name, any query block is given a name select#n. It is printed in EXPLAIN EXTENDED output:

Warnings:

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

At the moment it is NOT yet possible to use it in the hint text:

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

3.2 QB_NAME in CTEs.

What QB_NAME(@name) is used in a CTE which is then used in multiple places? Hints that control @name will control the first use of the CTE.
(This "just happens" due to the way CTEs are implemented. We could address it when addressing VIEWs)

4. Effect of optimizer hints

Optimizer can be controlled by
1. Server variables - optimizer_switch, join_cache_level, etc, etc
2. Old-style hints
3. New-style hints

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

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

Should new-style hints override old-style hints? TODO.

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

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

that 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.
Examples: mdev35483-mrr-is-narrow.sql

5. List of hints

5.1 NO_RANGE_OPTIMIZATION hint

It's an index-level hint that disables range optimization for certain index(es):

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

5.2 NO_ICP hint

It's 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 ... 

5.3 MRR and NO_MRR hints

Index-level hint 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

5.4 BKA() and NO_BKA() hints

It's a query block or table-level hint.

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

This also enables BKAH. TODO what was the reason for this?

5.5 BNL() and NO_BNL() hints

In MySQL this controls hash join, so in MariaDB it controls BNL-H.

The implementation is "BNL() hint effectively increases join_cache_level up to 4 " .. for the table(s) it applies to)
(FIXED: this seems to fail a fairly trivial example: mdev35483-bnl-fails-why.txt )

5.6 MAX_EXECUTION_TIME() hint

A global-level hint to limit query execution time:
Example:

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

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

Note: if the @@max_statement_time is set, the hint will be ignored and warning produced.
TODO: This contradicts with the stated principle that "New-style hints are more specific than server variable settings, so they override the server variable settings".

6. Subquery hints

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

6.2 SEMIJOIN, NO_SEMIJOIN

Query block-level hint.
This controls conversion of subquery to semi-join and which semi-join strategies are allowed.

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

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