MySQL FORCE INDEX

Summary: in this tutorial, you will learn how to use MySQL FORCE INDEX statement to force the Query Optimizer to use specified named indexes.

Introduction to MySQL FORCE INDEX statement

The query optimizer is a component in the MySQL Database server that makes the most optimal execution plan for an SQL statement.

The query optimizer uses the available statistics to come up with the plan that has the lowest cost among all candidate plans.

For example, a query might request for products whose prices are between 10 and 80. If the statistics show that 80% of products have these price ranges, then it may decide that a full table scan is the most efficient.

However, if statistics show that very few products have these price ranges, then reading an index followed by table access could be faster and more efficient than a full table scan.

In case the query optimizer ignores the index, you can use the FORCE INDEX hint to instruct it to use the index instead.

The following illustrates the FORCE INDEX hint syntax:

SELECT * 
FROM table_name 
FORCE INDEX (index_list)
WHERE condition;Code language: SQL (Structured Query Language) (sql)

In this syntax, you put the FORCE INDEX clause after the FROM clause followed by a list of named indexes that the query optimizer must use.

MySQL FORCE INDEX example

We will use the products table from the sample database for demonstration.

The following statement shows the indexes for the products table:

SHOW INDEXES FROM products;
Code language: SQL (Structured Query Language) (sql)

To find the products whose prices are between 10 and 80, you use the following statement:

SELECT 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Code language: SQL (Structured Query Language) (sql)

As you can guess, to return the products the query optimizer had to scan the whole table because no index was available for the buyPrice column:

EXPLAIN SELECT 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Code language: SQL (Structured Query Language) (sql)

Let’s create an index for the buyPrice column:

CREATE INDEX idx_buyprice ON products(buyPrice); Code language: SQL (Structured Query Language) (sql)

And execute the query again:

EXPLAIN SELECT 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;
Code language: SQL (Structured Query Language) (sql)

Surprisingly, the query optimizer did not use the index for the buyPrice column even though the index exists. The reason is that the query returns 94 rows out of 110 rows of the products table, therefore, the query optimizer decided to perform a full table scan.

To force the query optimizer to use the idx_buyprice index, you use the following query:

SELECT 
    productName, buyPrice
FROM
    products 
FORCE INDEX (idx_buyPrice)
WHERE
    buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;Code language: SQL (Structured Query Language) (sql)

This time, the index was used for finding the products as shown in the following EXPLAIN statement:

EXPLAIN SELECT 
    productName, buyPrice
FROM
    products 
FORCE INDEX (idx_buyprice)
WHERE
    buyPrice BETWEEN 10 AND 80
ORDER BY buyPrice;Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this tutorial, you have learned how to use the MySQL FORCE INDEX hint to force the query optimizer to use a list of named indexes.