Difference Between FORCE INDEX and USE INDEX in MySQL

This article explains how MySQL processes index hints, specifically focusing on the behavioral differences between the FORCE INDEX and USE INDEX directives. By understanding how the MySQL query optimizer interprets these hints, database administrators and developers can make informed decisions to optimize query performance and control execution plans effectively.

Understanding Index Hints in MySQL

MySQL utilizes a cost-based optimizer to determine the most efficient way to execute a query, including whether to use an index and which one to choose. However, due to outdated table statistics or complex query structures, the optimizer may occasionally select a suboptimal execution plan. Index hints allow developers to guide the optimizer’s decision-making process.

The two most common index hints are USE INDEX and FORCE INDEX. While they appear similar, they exert different levels of influence over the optimizer.

How USE INDEX Works

The USE INDEX hint advises MySQL to consider only the specified list of indexes to find rows in the table.

SELECT * FROM orders USE INDEX (idx_customer_id) WHERE customer_id = 101;

When you use USE INDEX, you are limiting the optimizer’s choices. Instead of evaluating all available indexes on the table, the optimizer will only choose from the specified index (or indexes) and the primary key.

However, USE INDEX is ultimately a recommendation. If the MySQL optimizer calculates that using the specified index is more expensive than performing a full table scan, it will ignore the index hint and opt for a full table scan instead.

How FORCE INDEX Works

The FORCE INDEX hint acts as a much stronger directive than USE INDEX.

SELECT * FROM orders FORCE INDEX (idx_customer_id) WHERE customer_id = 101;

Like USE INDEX, FORCE INDEX restricts the optimizer to considering only the named indexes. The key difference lies in how MySQL evaluates a full table scan. When FORCE INDEX is applied, the optimizer considers a table scan to be astronomically expensive. As a result, the optimizer will practically never choose a table scan if there is any technically feasible way to use the forced index to resolve the query.

The optimizer will only ignore a FORCE INDEX hint if the index cannot physically be used for the query—for example, if the query’s WHERE clause does not reference any columns present in the forced index.

Key Differences: USE INDEX vs. FORCE INDEX

Feature USE INDEX FORCE INDEX
Primary Function Restricts index choices to the specified list. Restricts index choices and discourages table scans.
Optimizer Discretion High. The optimizer can fall back to a full table scan if it is deemed cheaper. Low. The optimizer will avoid a table scan at all costs unless the index is unusable.
Best Use Case When you want to guide the optimizer but still allow a fallback to a table scan for safety. When the optimizer incorrectly chooses a table scan due to skewed or outdated statistics.

Best Practices for Using Index Hints

While index hints are powerful tools for resolving immediate performance bottlenecks, they should be used with caution:

  1. Keep Statistics Updated: Before hardcoding index hints, attempt to run ANALYZE TABLE to update the optimizer’s statistics. This often resolves the issue without code changes.
  2. Maintenance Overhead: Hardcoded index hints can become technical debt. If you rename or drop an index that is referenced in a hint, your queries will fail with a syntax error.
  3. Monitor Schema Changes: If the underlying data distribution changes significantly, a forced index that was once fast may eventually perform worse than a modern execution plan generated by the optimizer.