Skip to content

CONCEPT Cited by 1 source

MySQL index hint

A MySQL index hint is a SQL-level directive that instructs the query optimiser which index to use, or not use, or must use — overriding the planner's own choice. The three flavours are USE INDEX (name) (suggest), FORCE INDEX (name) (mandate), and IGNORE INDEX (name) (exclude). Hints are the last-resort escape hatch when the planner picks the wrong index and no amount of stats refresh or query rewriting changes the plan.

(Source: sources/2026-04-21-planetscale-why-isnt-mysql-using-my-index.)

Syntax

-- Suggest (optimiser can fall back to table scan if
-- it judges the suggested index worse than scan):
SELECT * FROM people USE INDEX (state)
  WHERE first_name = 'Aaron' AND state = 'TX';

-- Force (excludes table scan as a plan option):
SELECT * FROM people FORCE INDEX (state)
  WHERE first_name = 'Aaron' AND state = 'TX';

-- Exclude (treat as if this index doesn't exist):
SELECT * FROM people IGNORE INDEX (state)
  WHERE first_name = 'Aaron' AND state = 'TX';

The hint goes immediately after the table reference and can name one or multiple indexes. See the MySQL manual on Index Hints for the full syntax (optional scope clauses like FOR JOIN, FOR ORDER BY, FOR GROUP BY).

Francis's worked regression

Applied to the 491,583-row people table with first_name + state indexes available:

EXPLAIN SELECT * FROM people USE INDEX (state)
  WHERE first_name = 'Aaron' AND state = 'TX';

-- | type | key   | rows   | Extra       |
-- | ref  | state |  246944 | Using where |

Without the hint, the optimiser picks first_name (rows: 180). With the hint forcing state, the planner walks the much-less-selective state index first, scanning an estimated 246,944 rows — a ~1,370× regression on a query where the optimiser's default choice was correct.

The lesson: hints override the optimiser, not replace its judgement. A hint that was right when data was uniformly distributed can become catastrophically wrong after a tenant-mix shift changes selectivity.

When hints are appropriate

If you're entirely sure that the optimizer is wrong and you're right, you can force an index to be used. [...] Taking control away from the optimizer should be done with caution. If you understand what the optimizer is doing and why it's making a bad choice, telling it which index to use is a good escape hatch.

— Aaron Francis, Why isn't MySQL using my index?

Legitimate use cases:

  • Stats-refresh blind spot — after a mass insert of skewed data, stats are stale and auto-refresh hasn't triggered. ANALYZE TABLE is the primary fix; FORCE INDEX is a tactical workaround if stats refresh is not possible (read-replica without analyze permissions, for example).
  • Planner regression after upgrade — a new MySQL minor version changes cost estimation and the previously- correct plan flips to a wrong plan. Temporarily pin the plan with a hint while filing the regression.
  • Known skew — on a skewed column where you know more about the data than the stats can express, force the narrowing index.
  • Testing / benchmarking — verify that an index you think is useless really is useless, or measure the cost of alternative plans under identical conditions.

Why caution — the data-drift risk

Aaron Francis's rule:

Remember that as your data changes over time, you'll need to reevaluate if forcing a particular index is still the most performant option.

— Aaron Francis, Why isn't MySQL using my index?

A forced index is a point-in-time assertion about data distribution frozen into the query. When distribution changes — a customer growth pattern shifts, a bulk import adds 50% more rows to one tenant, a feature launch changes a categorical column's value mix — the hint becomes outdated and cannot adapt. The planner would have.

Operational discipline:

  1. Log every forced-index hint as a tech-debt item tagged with the reason, the date added, and the data- distribution assumption it encodes.
  2. Audit hints quarterly: EXPLAIN the query with and without the hint and compare. If the optimiser now picks the same (or better) plan unhinted, remove the hint.
  3. Prefer data-distribution fixes (histogram stats, partial index via functional column, schema split) over long-lived hints.

USE INDEX vs FORCE INDEX — the subtle difference

  • USE INDEX (a, b, c) tells the planner "consider only these indexes from the set of secondary indexes" — but the planner can still pick a full table scan if it estimates the scan is cheaper than any of the suggested indexes.
  • FORCE INDEX (a) goes further: it prices the table scan out of the consideration set entirely. The planner will use a even if a scan would be faster.

Most hint uses want FORCE INDEX semantics — "I'm asserting the index is correct, don't second-guess me". USE INDEX is softer but rarely useful in practice: if you're hinting, you usually don't want the scan fallback.

IGNORE INDEX — the negative case

IGNORE INDEX (x) is the inverse: it removes x from consideration, useful when an index is wrong for this specific query but you can't drop it (other queries need it). Common cases:

  • A composite index the planner keeps picking on a query pattern where a single-column index is better.
  • Historical indexes that produce sub-optimal plans on a subset of queries while still being valuable for others — treat-as-invisible-per-query via IGNORE INDEX instead of flipping the index invisible globally.

Seen in

Last updated · 470 distilled / 1,213 read