MySQL FORCE INDEX

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

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 a 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:

1 2 3 4

SELECT * FROM table_name FORCE INDEX (index_list) WHERE condition;

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:

1	SHOW INDEXES FROM products;

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

1 2 3 4 5 6 7 8

SELECT     productName,     buyPrice FROM     products WHERE     buyPrice BETWEEN 10 AND 80 ORDER BY buyPrice;

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

1 2 3 4 5 6 7 8

EXPLAIN SELECT     productName,     buyPrice FROM     products WHERE     buyPrice BETWEEN 10 AND 80 ORDER BY buyPrice;

Let’s create an index for the buyPrice column:

1	CREATE INDEX idx_buyprice ON products(buyPrice);

And execute the query again:

1 2 3 4 5 6 7 8

EXPLAIN SELECT     productName,     buyPrice FROM     products WHERE     buyPrice BETWEEN 10 AND 80 ORDER BY buyPrice;

Surprisingly, the query optimize 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:

1 2 3 4 5 6 7 8

SELECT     productName, buyPrice FROM     products FORCE INDEX (idx_buyPrice) WHERE     buyPrice BETWEEN 10 AND 80 ORDER BY buyPrice;

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

1 2 3 4 5 6 7 8

EXPLAIN SELECT     productName, buyPrice FROM     products FORCE INDEX (idx_buyprice) WHERE     buyPrice BETWEEN 10 AND 80 ORDER BY buyPrice;

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.