MySQL FORCE INDEX
This article describes how to force the query optimizer to use specified named indexes in MySQL.
Sometimes, although you have created an index, your SQL statement does not necessarily use the index. This is because the MySQL query optimizer made what it thought was a better choice.
The MySQL query optimizer is a component of the MySQL database server that formulates the best execution plan for SQL statements.
However, you can use the FORCE INDEX clause to tell the MySQL query optimizer to use the specified index.
MySQL FORCE INDEX syntax
To force an SQL statement to use a specified index, use the FORCE INDEX clause:
SELECT *
FROM table_name
FORCE INDEX (index_list)
WHERE condition;
Explanation:
- Place the
FORCE INDEXclause directly after the table name in theFROMclause. - The MySQL query optimizer must use an index from
index_list.
MySQL FORCE INDEX Examples
We’ll use the film table from the Sakila sample database for demonstration.
Here is the definition of film table:
DESC film;
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| film_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(128) | NO | MUL | NULL | |
| description | text | YES | | NULL | |
| release_year | year | YES | | NULL | |
| language_id | tinyint unsigned | NO | MUL | NULL | |
| original_language_id | tinyint unsigned | YES | MUL | NULL | |
| rental_duration | tinyint unsigned | NO | | 3 | |
| rental_rate | decimal(4,2) | NO | | 4.99 | |
| length | smallint unsigned | YES | | NULL | |
| replacement_cost | decimal(5,2) | NO | | 19.99 | |
| rating | enum('G','PG','PG-13','R','NC-17') | YES | | G | |
| special_features | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
13 rows in set (0.01 sec)The following statement shows the indexes in the film table:
SHOW INDEXES FROM film;
+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| film | 0 | PRIMARY | 1 | film_id | A | 1000 | NULL | NULL | | BTREE | | | YES | NULL |
| film | 1 | idx_title | 1 | title | A | 1000 | NULL | NULL | | BTREE | | | YES | NULL |
| film | 1 | idx_fk_language_id | 1 | language_id | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| film | 1 | idx_fk_original_language_id | 1 | original_language_id | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)Here, we found that the index idx_fk_language_id is on the language_id column.
To find English films, use the following statement:
SELECT *
FROM film
WHERE language_id = 1;
To view the execution plan for the statement, use the EXPLAIN statement:
EXPLAIN
SELECT *
FROM film
WHERE language_id = 1;
+----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | ALL | idx_fk_language_id | NULL | NULL | NULL | 1000 | 100.00 | Using where |
+----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)Here, the MySQL query optimizer does not use the idx_fk_language_id index. This is because all the films in the film table are in English, so the MySQL query optimizer specifies a full table scan.
To force the query optimizer to use the idx_fk_language_id index, use the following query with FORCE INDEX. The following statement shows the execution plan:
EXPLAIN
SELECT *
FROM film
FORCE INDEX (idx_fk_language_id)
WHERE language_id = 1;
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | film | NULL | ref | idx_fk_language_id | idx_fk_language_id | 1 | const | 1000 | 100.00 | NULL |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)Now, the MySQL query optimizer uses the idx_fk_language_id index.
Conclusion
The MySQL FORCE INDEX clause tells the MySQL query optimizer to use the specified index.