Skip to content

PATTERN Cited by 1 source

Force-index escape hatch

Intent

When you have proven that the MySQL optimiser is picking the wrong index and no stats refresh or query rewrite fixes it, pin the index choice with USE INDEX (name) or FORCE INDEX (name) — and log the override as tech debt to re-evaluate as data distribution shifts.

Context

You have a query that:

  1. Has been diagnosed with EXPLAIN to confirm a wrong-index plan.
  2. Returns correct results with either the wrong index or the right one — performance is the only issue.
  3. Does not improve after running ANALYZE TABLE to refresh index stats.
  4. Cannot be rewritten to be structurally forced onto the right index (e.g. by reordering WHERE predicates or adding covering-index coverage).
  5. Has a large blast radius if it runs slow — core product path, dashboard query with SLA, etc.

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

Forces

  • Aaron Francis's framing: "The optimizer is a complicated and sophisticated piece of software written by talented people over decades. It usually makes the right decision. Usually... but not always."
  • Forced hints are point-in-time assertions about data distribution — they cannot adapt when distribution shifts. A hint correct in 2024 can be catastrophically wrong in 2026 after a customer-mix change.
  • Removing a hint in production is risky — the query might have been tolerated-slow with the hint for so long that nobody knows if the optimiser's unhinted plan is fast enough now.

Solution

Step 1 — Verify the plan is wrong

Run EXPLAIN on both versions:

-- Current (optimiser's choice):
EXPLAIN SELECT ... FROM tbl WHERE ...;
-- type, key, rows

-- Forced alternative:
EXPLAIN SELECT ... FROM tbl USE INDEX (better_idx) WHERE ...;
-- type, key, rows

Compare rows estimates and measured runtimes. The forced alternative should be orders of magnitude better — otherwise the override isn't worth the tech-debt.

Step 2 — Refresh stats first

Before committing to a hint, rule out stale stats:

ANALYZE TABLE tbl;

Re-run EXPLAIN. If the optimiser now picks the better index on its own, the root cause was stats staleness, not a planner bug. Hint not needed.

Step 3 — Apply the hint

SELECT ... FROM tbl FORCE INDEX (better_idx) WHERE ...;

Prefer FORCE INDEX over USE INDEX for assertion semantics (the former prices the table scan out of consideration; the latter is a weaker suggestion). See concepts/mysql-index-hint for full syntax nuances.

Step 4 — Record the override

For every forced-index hint added, record in a tech-debt tracker:

  • Query pattern — SQL fingerprint of the hinted query.
  • Reason — "Skewed distribution on tenant_id causes optimiser to prefer the less-selective index — measured 1,370× regression."
  • Date added — anchor for re-evaluation cadence.
  • Data-distribution assumption — what has to be true for the hint to remain correct (e.g. "tenant-A dominates >50% of rows").
  • Re-evaluation trigger — quarterly check, after schema migration, after major customer onboarding.

Step 5 — Audit on cadence

Quarterly (or after major data shifts):

-- Re-run EXPLAIN on the hinted query with and without the hint:
EXPLAIN SELECT ... FROM tbl WHERE ...;           -- unhinted
EXPLAIN SELECT ... FROM tbl FORCE INDEX (x) ...; -- hinted

If the optimiser now picks the same (or better) plan unhinted, remove the hint. Data distribution has evolved; the planner can handle it.

Consequences

Benefits

  • Pins a known-good plan for queries on the critical path when the optimiser cannot be trusted.
  • Bounded blast radius — the hint only affects the one query, not the whole schema.
  • Reversible — removing a hint is a SQL-level change, not a schema migration.

Costs

  • Non-adaptive — the hint cannot track data distribution changes. Aaron Francis's warning: "Remember that as your data changes over time, you'll need to reevaluate if forcing a particular index is still the most performant option."
  • Hides the root cause — a forced hint often papers over a stats-staleness or schema-design issue that would be fixed more durably elsewhere (histogram statistics, partial-index via generated column, schema split on skewed columns).
  • Spread — one hint tends to breed more as each planner-disagreement produces a fresh override. Audit to keep the hint count bounded.

Alternatives

  • Refresh stats — ANALYZE TABLE should be the first attempt.
  • Histogram statistics (MySQL 8.0+) — ANALYZE TABLE ... UPDATE HISTOGRAM ON skewed_col gives the optimiser a value-frequency distribution instead of a single average cardinality. Often fixes skewed-column regressions structurally.
  • Add a covering index — an index covering all predicate + projection columns makes the plan choice unambiguous.
  • Query rewrite — predicate reordering, subquery unflattening, or STRAIGHT_JOIN to lock join order.
  • Functional index / generated column — pre-compute the narrowing value and index that, so the planner has a single obvious choice.

Known uses

Last updated · 470 distilled / 1,213 read