Index-Level Hints

Overview

These hints follow the syntax of index-level hints which includes two variants:

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

They enable or disable the specified indexes for index scans for specific operations. The following rules apply:

• for enabling (or whitelisting) hints (GROUP_INDEX, JOIN_INDEX, INDEX, ORDER_INDEX):

  • if the hint body includes only a table name and no index names, the optimizer is free to choose any applicable index of that table;

  • if the body includes both a table name and one or more index names, the optimizer will only consider the specified indexes for that table;

• for disabling (or blacklisting) hints (NO_GROUP_INDEX, NO_JOIN_INDEX, NO_INDEX, NO_ORDER_INDEX):

  • if the hint body includes only a table name and no index names, the optimizer will not use any indexes for that table;

  • if the body includes both a table name and one or more index names, the optimizer will not use the specified indexes for that table but can use any other not listed.

GROUP_INDEX(), NO_GROUP_INDEX()

These hints are available from MariaDB 12.1.

These hints enable or disable the specified indexes for index scans for GROUP BY operations but do not affect other operations: access to table data and sorting. Equivalent to old-style hints FORCE INDEX FOR GROUP BY and IGNORE INDEX FOR GROUP BY.

Examples

-- Do not use indexes of `t1` for the `GROUP BY` operation
SELECT /*+ NO_GROUP_INDEX(t1)*/ a, MAX(b) FROM t1 GROUP BY a;

-- Force `idx_ab` index to be used for `GROUP BY`, although other 
-- indexes may be also applicable
SELECT /*+ GROUP_INDEX(t1 idx_ab) */ a, MAX(b) FROM t1 GROUP BY a;

JOIN_INDEX(), NO_JOIN_INDEX()

These hints are available from MariaDB 12.1.

Enable or disable the specified indexes for an access method (range, ref, etc.). Equivalent to old-style hints FORCE INDEX FOR JOIN and IGNORE INDEX FOR JOIN.

Example

-- Force the optimizer to consider only `idx1` and `idx2` indexes to access
-- table `t1` data, although there may be other indexes applicable
SELECT /*+ JOIN_INDEX(t1 idx1, idx2)*/a FROM t1 WHERE a > 1 AND a < 3;

INDEX(), NO_INDEX()

These hints are available from MariaDB 12.1.

Enable or disable the specified indexes for all scopes (access to table data, grouping and sorting). Equivalent to old-style hints FORCE INDEX and IGNORE INDEX.

Examples

-- Forbid using indexes `i_a` and `i_ab` but do not prevent the optimizer
-- from using any other applicable indexes
SELECT /*+ NO_INDEX(t1 i_a, i_ab)*/ a, MAX(b) FROM t1 WHERE a > 1 AND a < 3 GROUP BY a;

-- Force the optimizer to use index `i_a`
UPDATE /*+ INDEX(t1 i_a) */ t1 SET d = 1 WHERE a = 1 AND b = 2 AND c = 3;

-- Do not allow the optimizer to use index `i_ab`, however any other
-- applicable index can be used
DELETE /*+ NO_INDEX(t1 i_ab) */ FROM t1 WHERE a = 1 AND b = 2 AND c = 3;

ORDER_INDEX(), NO_ORDER_INDEX()

These hints are available from MariaDB 12.1.

These hints enable or disable the specified indexes for sorting rows but do not affect other operations: access to table data and grouping. Equivalent to old-style hints FORCE INDEX FOR ORDER BY and IGNORE INDEX FOR ORDER BY.

Example

-- Forbid using of index `i_ab` for sorting. However, the optimizer is free
-- to use any other available index
SELECT /*+ NO_ORDER_INDEX(t1 i_ab) */ a FROM t1 ORDER BY a;

Combining Multiple Index Hints

Multiple index hints can be combined in a single query unless they conflict with each other. For example:

-- No indexes are allowed for grouping, index `i_ab` is forbidden for ordering,
-- but all indexes are usable for data access
SELECT /*+ NO_GROUP_INDEX(t1), NO_ORDER_INDEX(t1 i_ab) */ a
FROM t1 WHERE a > 1 AND a < 3 
GROUP BY a
ORDER BY a;

Here the hints do not conflict with each other because they target different operations.

A conflict may happen when the same class of hints is applied to a single table, for example:

SELECT /*+ JOIN_INDEX(t1 i_a) NO_JOIN_INDEX(t1 i_d) */ a FROM t1;

or when an umbrella hint INDEX/NO_INDEX is specified:

SELECT /*+ INDEX(t1 i_a) JOIN_INDEX(t1 i_d) */ a FROM t1 WHERE a < 3;

Here the warning will be generated:

Warning 4238    Hint JOIN_INDEX(`t1` `i_d`) is ignored as conflicting/duplicated (an index hint of the same type has already been specified for this table)

because INDEX hint is essentially a combination of JOIN_INDEX|GROUP_INDEX|ORDER_INDEX.

If a single SQL query includes both old-style and new-style index hints, old-style hints are silently ignored, for example:

SELECT /*+ INDEX(t1 i_a) */ * FROM t1 IGNORE INDEX(i_a) WHERE a = 1 AND b = 2 AND c = 3;

New-style INDEX(t1 i_a) has effect while old-style IGNORE INDEX(i_a) does not.

INDEX_MERGE(), NO_INDEX_MERGE()

This hint is available from MariaDB 12.2.

The INDEX_MERGE and NO_INDEX_MERGE optimizer hints provide granular control over the optimizer's use of index merge strategies. They allow users to override the optimizer's cost-based calculations and global switch settings, to force or prevent the merging of indexes for specific tables.

Syntax

/*+ INDEX_MERGE(table_name [index_name, ...]) */
/*+ NO_INDEX_MERGE(table_name [index_name, ...]) */

Behavior

The hints operate by modifying the set of keys the optimizer considers for merge operations. The specific behavior depends on whether specific index keys are provided within the hint.

INDEX_MERGE Hint

This hint instructs the optimizer to employ an index merge strategy.

• Without arguments: When specified as INDEX_MERGE(tbl), the optimizer considers all available keys for that table and selects the cheapest index merge combination.

• With specific keys: When specified with keys, for instance, INDEX_MERGE(tbl key1, key2), the optimizer considers only the listed keys for the merge operation. All other keys are excluded from consideration for index merging.

  The INDEX_MERGE hint overrides the global optimizer_switch. Even if a specific strategy (such as index_merge_intersection) is disabled globally, the hint forces the optimizer to attempt the strategy using the specified keys.

NO_INDEX_MERGE Hint

This hint instructs the optimizer to avoid index merge strategies.

• Without arguments: When specified as NO_INDEX_MERGE(tbl), index merge optimizations are completely disabled for the specified table.

• With specific keys: When specified with keys, for instance, NO_INDEX_MERGE(tbl key1), the listed keys are excluded from consideration. The optimizer may still perform a merge using other available keys. However, if excluding the listed keys leaves insufficient row-ordered retrieval (ROR) scans available, no merge is performed.

Algorithm Selection and Limitations

While these hints control which keys are candidates for merging, they do not directly dictate the specific merge algorithm (Intersection, Union, or Sort-Union).

• Indirect Control: You can influence the strategy indirectly by combining these hints with optimizer_switch settings, but specific algorithm selection is not guaranteed.

• Invalid Hints: If a hint directs the optimizer to use specific indexes, but those indexes do not provide sufficient ROR scans to form a valid plan, the server is unable to honor the hint. In this scenario, the server emits a warning.

Examples

In the following examples, the index_merge_intersection switch is globally disabled. However, the INDEX_MERGE hint forces the optimizer to consider specific keys (f2 and f4), resulting in an intersection strategy.

You can see that we disable intersection with NO_INDEX_MERGE for the following query and the behavior reflects in the EXPLAIN output. The query after that shows with the hint enabling merge–an intersection of f3,f4 is used. In the last example, a different intersection is used: f3,PRIMARY.

No intersection (no merged indexes):

MariaDB [test]> EXPLAIN SELECT /*+ NO_INDEX_MERGE(t1 f2, f4, f3) */ COUNT(*) FROM t1 WHERE f4 = 'h' AND f3 = 'b' AND f5 = 'i'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,f3,f4
          key: f3
      key_len: 9
          ref: const,const
         rows: 1
        Extra: Using index condition; Using where
1 row in set (0.009 sec)

Intersection of keys f3, f4:

MariaDB [test]> EXPLAIN SELECT /*+ INDEX_MERGE(t1 f2, f4, f3) */ COUNT(*) FROM t1 WHERE f4 = 'h' AND f3 = 'b' AND f5 = 'i'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index_merge
possible_keys: PRIMARY,f3,f4
          key: f3,f4
      key_len: 9,9
          ref: NULL
         rows: 1
        Extra: Using intersect(f3,f4); Using where; Using index
1 row in set (0.010 sec)

Intersection of keys PRIMARY, f3:

MariaDB [test]> EXPLAIN SELECT COUNT(*) FROM t1 WHERE f4 = 'h' AND f3 = 'b' AND f5 = 'i'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index_merge
possible_keys: PRIMARY,f3,f4
          key: f3,PRIMARY
      key_len: 9,4
          ref: NULL
         rows: 1
        Extra: Using intersect(f3,PRIMARY); Using where
1 row in set (0.006 sec)

NO_ICP()

This hint is available from MariaDB 12.0.

Disables Index Condition Pushdown for a specified table or a subset of its indexes. Effectively overrides the optimizer_switch setting index_condition_pushdown=on.

Examples

-- By default, ICP is enabled and we can see "Using index condition" in the "Extra" field of EXPLAIN output
> EXPLAIN SELECT * FROM tbl WHERE key_col1 BETWEEN 10 AND 11 AND key_col2 LIKE '%foo%';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | tbl   | range | key_col1      | key_col1 | 5       | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+

-- Hint disables ICP, so there is no more "Using index condition" in the EXPLAIN output
> EXPLAIN SELECT /*+ NO_ICP(tbl)*/ * FROM tbl WHERE key_col1 BETWEEN 10 AND 11 AND key_col2 LIKE '%foo%';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | tbl   | range | key_col1      | key_col1 | 5       | NULL |    2 |                       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+

-- Same effect is achieved by specifying both table name and index name
> EXPLAIN SELECT /*+ NO_ICP(tbl key_col1)*/ * FROM tbl WHERE key_col1 BETWEEN 10 AND 11 AND key_col2 LIKE '%foo%';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | tbl   | range | key_col1      | key_col1 | 5       | NULL |    2 |                       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+

NO_RANGE_OPTIMIZATION()

This hint is available from MariaDB 12.0.

An index-level hint that disables range optimization for certain index(es) or a whole table. There is no corresponding optimizer switch setting for this optimization.

Examples

-- By default, the optimizer chooses `PRIMARY` key for `range` access to `t1`
> EXPLAIN SELECT f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
id   | select_type | table |  type | possible_keys | key      | key_len |  ref | rows |  filtered   |     Extra
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1    |  SIMPLE     |  t3   | range | PRIMARY,f2_idx|  PRIMARY |    4    | NULL |   2  |     100.00  |  Using where; Using index
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+

-- Exclude `PRIMARY` key from indexes allowed for `range` access, `f2_idx` is used instead
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
id   | select_type | table |  type | possible_keys | key      | key_len |  ref | rows |  filtered   |     Extra
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1    |  SIMPLE     |  t3   | range | PRIMARY,f2_idx| f2_idx   |   4     |  NULL|  2   |   100.00    | Using where; Using index
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+

-- This hint excludes all indexes applicable for `range` access, so the optimizer switches to the `index` access
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
id   | select_type | table |  type | possible_keys | key      | key_len |  ref | rows |  filtered   |     Extra
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1    | SIMPLE      | t3    | index | PRIMARY,f2_idx|  PRIMARY |  4      | NULL | 56   |   4.11      | Using where; Using index

-- The hint disables range access for any indexes of `t3`, `index` access is used as in the test above
EXPLAIN EXTENDED SELECT /*+ NO_RANGE_OPTIMIZATION(t3) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
id   | select_type | table |  type | possible_keys | key      | key_len |  ref | rows |  filtered   |     Extra
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1    | SIMPLE      | t3    | index | PRIMARY,f2_idx|  PRIMARY |  4      | NULL | 56   |   4.11      | Using where; Using index

MRR(), NO_MRR()

These hints are available from MariaDB 12.0.

These hints allow to control the multi-range read optimization, effectively overriding the optimizer switch setting mrr=[on|off].

Examples

-- MRR optimization is disabled by optimizer_switch setting:
set optimizer_switch='mrr=off';

--Turn on MRR for `t1` table
SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;

--Turn on MRR for `IDX2` of table `t1`
SELECT /*+ MRR(t1 IDX2) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;

-- MRR optimization is enabled by optimizer_switch setting (default setting):
set optimizer_switch='mrr=on';

-- Disable multi-range read when accessing table `t1`
SELECT /*+ NO_MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;

ROWID_FILTER(), NO_ROWID_FILTER()

These hints are available from MariaDB 12.1.

Allows to control the ROWID filter optimization. Effectively they override the optimizer switch setting rowid_filter=[on|off].

• Rules for ROWID_FILTER() hint:

  • if the hint body includes only a table name and no index names, the optimizer is free to choose any indexes applicable for rowid-filtering optimization;

  • if the body includes both a table name and one or more index names, the optimizer will only consider the specified indexes for rowid-filtering;

  • the hint forces the use of rowid-filtering (if applicable) despite being less efficient than not using the optimization (from the optimizer's point of view);

• Rules for NO_ROWID_FILTER() hint:

  • if the hint body includes only a table name and no index names, the optimizer will not use any indexes for rowid-filtering;

  • if the body includes both a table name and one or more index names, the optimizer will not use the specified indexes for rowid-filtering but can use any other not listed.

Examples

SELECT /*+ rowid_filter(t1)*/ a FROM t1 WHERE a < 'e' AND b > 't';
SELECT /*+ no_rowid_filter(t1)*/ a FROM t1 WHERE a < 'e' AND b > 't';
SELECT /*+ rowid_filter(t1 key_a, key_b)*/ a FROM t1 WHERE a < 'e' AND b > 't';
SELECT /*+ no_rowid_filter(t1 key_c)*/ a FROM t1 WHERE a < 'e' AND b > 't';

Interaction with Other Index Hints

If [NO_]INDEX(), [NO_]JOIN_INDEX() hints are specified for the same table, then only indexes whitelisted by INDEX() or JOIN_INDEX() can be considered for rowid filters. ROWID_FILTER( hint cannot force the use of indexes that are disabled by NO_INDEX() / NO_JOIN_INDEX() or omitted from INDEX() / JOIN_INDEX(). For example, if one index should be used for data access and another for a rowid filter, the filter index must be mentioned in both hints:

SELECT /*+ index(t1 key_a, key_b) rowid_filter(t1 key_b) */ a FROM t1 ....