In this tutorial, we will learn about the MySQL force index hint. It is very similar to the USE INDEX clause. Therefore, it will be easy to learn as well as to implement if you know about the USE INDEX hint. However, having information about the USE INDEX is not necessary to learn about the force index.
Also read: MySQL USE INDEX For Index Hints
Introduction to MySQL FORCE INDEX
As you know, whenever there is an index available on the table, the query optimizer tries to utilize it for fetching the records in the most optimal way.
The query optimizer will always try to come up with the most optimal execution way to find the result.
For example, if there is an index available on a column, the query optimizer will use it to find the records in an efficient way. However, if there are more than half of the total rows are in the result, the query optimizer will decide that the full table scan is required and it’s the most efficient way.
It’s not necessary that the query optimizer will always do a full scan if the number of records in the output is greater than 50% of the total records.
In simple words, when one of the named indexes cannot be utilised to locate rows in the table, a table scan is performed.
So, if the query optimizer ignores the named index and you think that the query optimizer might use one of the available indexes, you can use the FORCE INDEX hint.
You will understand it in a better way further in this tutorial by the examples.
MySQL FORCE INDEX Syntax
The following syntax is used to make use of the FORCE INDEX hint.
SELECT col_names
FROM table_name
FORCE INDEX (index_list)
WHERE condition;Code language: SQL (Structured Query Language) (sql)Note that, you can not combine the FORCE INDEX and the USE INDEX.
The FORCE INDEX can consist of one or more index names.
MySQL FORCE INDEX Example
To demonstrate the example of the FORCE INDEX, we will use the following table schema and the data.
We have no index currently created on any column for the table marks.
Let’s write a query to find the records having marks between 50 and 100.
EXPLAIN SELECT * FROM marks
WHERE marks between 50 and 100 \GCode language: SQL (Structured Query Language) (sql)
As you guessed it right, the full table scan is done to find the expected result because we don’t have an index on the marks column.
Now let’s create an index on the marks column.
CREATE INDEX ind_marks ON marks(marks);Code language: SQL (Structured Query Language) (sql)The index is created. Let’s run the previous query again to find the records having marks between 50 and 100.
EXPLAIN SELECT * FROM marks
WHERE marks between 50 and 100 \GCode language: SQL (Structured Query Language) (sql)
In the result above you can see, that the query optimizer didn’t use the ind_marks index even though it exists.
The reason behind ignoring the index is, that the query returns 18 records out of 24 records. Therefore, instead of using the index, the query optimizer decides the full table scan is required.
In this case, if you want the query optimizer to use the ind_marks index forcefully, you can use the FORCE INDEX hint.
Check the query below and result now-
EXPLAIN SELECT * FROM marks
FORCE INDEX(ind_marks)
WHERE marks between 50 and 100\GCode language: SQL (Structured Query Language) (sql)
As you can see in the result above, the query optimizer now uses the index that we have forced it to use.
Now you might have understood how the FORCE INDEX hint actually works.
Conclusion
In this tutorial, we have learned about the FORCE INDEX hint. It is very similar to the USE INDEX hint but helps in the situation where the full table scan is done instead of using the available index. If this tutorial helped you, don’t forget to share it with your friends!
References
MySQL official documentation on index hints.