How to work with Index Hints in MySQL

MySQL index hints are used in SELECT statement conditions to manage indexes using the USE INDEX, FORCE INDEX and IGNORE INDEX commands. These commands transfer the index information to the optimizer and modify the control of the query execution. If used correctly, index hints will indicate which index should be used and can minimize execution time. In this article, we discuss how to use index hints in MySQL.

Enhancing queries using index hints can only be used on SELECT statements. Even though it can be used in UPDATE statements without an error, there is no effect.  

MySQL Index Hints Syntax

The following is the syntax for using index hints in MySQL:

1. SELECT column(s)
3. FROM table(s)
5. { USE | IGNORE | FORCE } INDEX ( index1, index2, … )
7. WHERE condition(s)

For the following example, the SALES table will be used with the index hints syntax, forcing SQL to use only the indexes mentioned in the USE INDEX command to return the rows within the table:

SALES Table:

SalesID	CustomerID	ZoneID	ProductID	ProductPrice	PrdQty	Total
1010	111011	1	10	20	3	60
1011	111012	3	20	130	1	130
1012	111011	1	10	20	5	100
1013	111013	2	30	150	2	300

USE INDEX

In the USE INDEX command, the parameters specified are indexes which are recommended to be used. Note that indexes listed from this command can be avoided, which will imply that the query plan will not use any indexes for this execution.

Our sample query is as follows:

1. SELECT * FROM SALES USE INDEX (SalesIndX, CustIndX)
3. WHERE SalesID = 1010 AND CustId = 111011 AND ZoneId = 1;

IGNORE INDEX

In the IGNORE INDEX command, the parameters specified refer to indexes that should be avoided. Using this command will force MySQL to use all the other indexes in the existing table but not the one mentioned in the query statement:

Our sample query is as follows:

1. SELECT * FROM SALES IGNORE INDEX (ZoneIndX)
3. WHERE SalesID = 1010 AND CustId = 111011 AND ZoneId = 1;

FORCE INDEX

In the FORCE INDEX command, the parameters specified are the only indexes allowed to be used in the query execution. This option should be used when the optimizer does not use the correct index that it’s being defined in the WHERE condition, even if we used the USE INDEX option already.

Our sample query is as follows:

1. SELECT * FROM SALES FORCE INDEX (ProductPriceIndX)
3. WHERE ProductPrice BETWEEN 100 AND 200;

Note: The FORCE INDEX command is used to avoid full table scans on tables that hold big data amounts.

USE INDEX / IGNORE INDEX / FORCE INDEX Conditions

The following specifies how the index hints commands are used:

• In the FORCE INDEX and IGNORE INDEX command, the index list must be declared. Let’s look at the following query:

1. SELECT * FROM table_name FORCE INDEX;

 This syntax will result in the following error:

[Error: PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FORCE INDEX' at line 1]

•  The USE INDEX and FORCE INDEX command cannot be used together, accessing the same table. Let’s look at the following query:

1. SELECT * FROM table_name USE INDEX (index1) FORCE INDEX (index1);

 This syntax will result in the following error:

[Error: PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USE INDEX (index1) FORCE INDEX (index1)' at line 1]

•  We can have the USE INDEX and IGNORE INDEX commands in the same statement. Let’s look at the following query:

1. SELECT * FROM table_name USE INDEX (index1) IGNORE INDEX (index2);

• Multiple index hints of the same type can be used in the same query. Let’s look at the following query:

1. SELECT * FROM table_name USE INDEX (index1) USE INDEX (index2);

• To define the scope of the index hint, add the clause FOR. There are three specific uses for it: FOR JOIN, used for join processes, FOR ORDER BY and FOR GROUP BY to manipulate how indexes work while grouping or sorting rows.

If the scope is not specified, MySQL will use the index hints on all of these.

1. SELECT * FROM table_name
3. USE INDEX FOR JOIN (index1)
5. USE INDEX FOR ORDER BY (index2)
7. USE INDEX FOR GROUP BY (index2);