Index Hints: How to Force Query Plans

The optimizer is largely cost-based and will try to choose the optimal plan for any query. However in some cases it does not have enough information to choose a perfect plan and in these cases you may have to provide hints to force the optimizer to use another plan.

You can examine the query plan for a SELECT by writing EXPLAIN before the statement. SHOW EXPLAIN shows the output of a running query. In some cases, its output can be closer to reality than EXPLAIN.

For the following queries, we will use the world database for the examples.

Setting up the World Example Database

Download it from

Install it with:

or

Forcing Join Order

You can force the join order by using either in the or part.

The simplest way to force the join order is to put the tables in the correct order in the FROM clause and use SELECT STRAIGHT_JOIN like so:

If you only want to force the join order for a few tables, useSTRAIGHT_JOIN in the FROM clause. When this is done, only tables connected with STRAIGHT_JOIN will have their order forced. For example:

In both of the above cases Country will be scanned first and for each matching country (one in this case) all rows in City will be checked for a match. As there is only one matching country this will be faster than the original query.

The output of for the above cases is:

This is one of the few cases where ALL is ok, as the scan of theCountry table will only find one matching row.

Forcing Usage of a Specific Index for the WHERE Clause

In some cases the optimizer may choose a non-optimal index or it may choose to not use an index at all, even if some index could theoretically be used.

In these cases you have the option to either tell the optimizer to only use a limited set of indexes, ignore one or more indexes, or force the usage of some particular index.

USE INDEX: Use a Limited Set of Indexes

You can limit which indexes are considered with the option.

The default is 'FOR JOIN', which means that the hint only affects how theWHERE clause is optimized.

USE INDEX is used after the table name in the FROM clause.

Example:

This produces:

If we had not used , the Name index would have been inpossible keys.

IGNORE INDEX: Don't Use a Particular Index

You can tell the optimizer to not consider some particular index with the option.

This is used after the table name in the FROM clause:

This produces:

The benefit of using IGNORE_INDEX instead of USE_INDEX is that it will not disable a new index which you may add later.

Also see for an option to specify in the index definition that indexes should be ignored.

FORCE INDEX: Forcing an Index

to be used is mostly useful when the optimizer decides to do a table scan even if you know that using an index would be better. (The optimizer could decide to do a table scan even if there is an available index when it believes that most or all rows will match and it can avoid the overhead of using the index).

This produces:

FORCE_INDEX works by only considering the given indexes (like withUSE_INDEX) but in addition it tells the optimizer to regard a table scan as something very expensive. However if none of the 'forced' indexes can be used, then a table scan will be used anyway.

Index Prefixes

When using index hints (USE, FORCE or ), the index name value can also be an unambiguous prefix of an index name.

Forcing an Index to be Used for ORDER BY or GROUP BY

The optimizer will try to use indexes to resolve and .

You can use , and as in the WHERE clause above to ensure that some specific index used:

This is used after the table name in the FROM clause.

Example:

This produces:

Without the option we would have 'Using where; Using temporary; Using filesort' in the 'Extra' column, which means that the optimizer would created a temporary table and sort it.

Help the Optimizer Optimize GROUP BY and ORDER BY

The optimizer uses several strategies to optimize and :

• Resolve with an index:

  • Scan the table in index order and output data as we go. (This only works if the / can be resolved by an index after constant propagation is done).

• Filesort:

  • Scan the table to be sorted and collect the sort keys in a temporary file.

A temporary table will always be used if the fields which will be sorted are not from the first table in the order.

• Use a temporary table for :

  • Create a temporary table to hold the result with an index that matches the fields.

  • Produce a result row

  • If a row with the key exists in the temporary table, add the new result row to it. If not, create a new row.

Forcing/Disallowing TemporaryTables to be Used for GROUP BY:

Using an in-memory table (as described above) is usually the fastest option for if the result set is small. It is not optimal if the result set is very big. You can tell the optimizer this by usingSELECT SQL_SMALL_RESULT or SELECT SQL_BIG_RESULT.

For example:

produces:

while:

produces:

The difference is that with SQL_SMALL_RESULT a temporary table is used.

Forcing Usage of Temporary Tables

In some cases you may want to force the use of a temporary table for the result to free up the table/row locks for the used tables as quickly as possible.

You can do this with the SQL_BUFFER_RESULT option:

This produces:

Without SQL_BUFFER_RESULT, the above query would not use a temporary table for the result set.

Optimizer Switch

In we added an which allows you to specify which algorithms will be considered when optimizing a query.

See the section for more information about the different algorithms which are used.

See Also

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