PATTERN Cited by 1 source

EXPLAIN for index verification¶

Intent¶

Before asking why MySQL isn't using an index, first verify that it isn't — prepend EXPLAIN to the suspect query and read possible_keys, key, type, and rows to mechanically confirm the index-miss before investigating root causes.

Context¶

You wrote a query, created an index you believe will speed it up, and the query is still slow. Before diagnosing any of the eight-plus reasons an index can be ignored — insufficient selectivity, stale stats, table scan being faster, leading wildcard, leftmost-prefix violation, type mismatch, obfuscation, invisibility — you need to know which question to ask:

Is the index considered? → look at possible_keys.
Is the index chosen? → look at key.
Is the planner walking it as expected? → look at type + rows.

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

Forces¶

Without EXPLAIN you're guessing at the planner's behaviour — and guessing wrong leads to fix for reason B when actually it's reason A.
Aaron Francis's framing: "Before you can determine why your index isn't being used, you must first determine that your index isn't being used."
Production latency pressure pushes engineers to try rewrites without diagnosis — which often breaks other query paths while fixing the target one.

Solution¶

Four-column diagnostic read every index-question query run:

Column	What to read	What the value tells you
`possible_keys`	Comma-separated list of indexes the planner considered.	If your index isn't here, it's structurally ineligible — check wildcard-prefix, leftmost-prefix, type-mismatch, obfuscation, invisibility.
`key`	The single index the planner actually picked.	If your index is in `possible_keys` but isn't `key`, the planner considered and rejected it — check selectivity, stats freshness, or whether the table scan is actually faster.
`type`	Access method — `const`, `eq_ref`, `ref`, `range`, `index`, `ALL`.	`ref` / `const` / `range` = index-using. `ALL` = full table scan. See concepts/mysql-access-type.
`rows`	Estimated rows examined.	Compare against actual result-set size. Orders-of-magnitude gap indicates the plan is fetching far more than needed.

Worked example from Francis¶

EXPLAIN SELECT * FROM people WHERE first_name = 'Aaron';

-- | type | possible_keys | key        | key_len | ref   | rows | filtered |
-- | ref  | first_name    | first_name | 202     | const |  180 |   100.00 |

possible_keys: first_name → considered ✓
key: first_name → chosen ✓
type: ref → walking via index, equality lookup ✓
rows: 180 → narrow enough to trust ✓

Query is using the index as expected.

Counter-example — wildcard prefix¶

EXPLAIN SELECT * FROM people WHERE first_name LIKE '%ron';

-- | type | possible_keys | key  | rows   | Extra       |
-- | ALL  | NULL          | NULL | 493889 | Using where |

possible_keys: NULL → not considered — structural ineligibility.
Root cause: leading wildcard.
Fix class: rewrite to anchored pattern, use fulltext index, or accept table scan.

Counter-example — leftmost-prefix miss¶

-- With composite index multi(first_name, state):
EXPLAIN SELECT * FROM people WHERE state = 'TX';

-- | type | possible_keys | key  | rows   |
-- | ALL  | NULL          | NULL | 493889 |

possible_keys: NULL even though multi exists — the leftmost-prefix rule rules it out.
Fix class: add first_name to the predicate, or create a single-column index on state.

Counter-example — considered but not chosen¶

EXPLAIN SELECT * FROM people
  WHERE first_name = 'Aaron' AND state = 'TX';

-- | possible_keys    | key        | rows | filtered |
-- | first_name,state | first_name |  180 |    50.00 |

possible_keys: first_name, state → both considered.
key: first_name → optimiser picked the more selective one.
This is the "phase A" case — the planner evaluated alternatives and picked the one it judged best via selectivity.

Extended diagnostic columns¶

When EXPLAIN isn't enough, extended forms surface more:

EXPLAIN FORMAT=JSON — full cost breakdown, per-step row estimates, pruned-plan alternatives.
EXPLAIN ANALYZE — actually runs the query and reports measured vs estimated rows. See concepts/mysql-explain-analyze.
SHOW WARNINGS immediately after EXPLAIN — shows the rewritten query the planner actually ran, including subquery flattening and constant folding.

Consequences¶

Benefits¶

Correctly diagnosed before fixed. Eliminates guessing wasted on the wrong reason.
Cheap — EXPLAIN adds no I/O beyond planning.
Universal — every MySQL installation supports it; no extension or elevated privilege required.

Costs / caveats¶

rows is an estimate derived from sampled stats; it can be wildly off if stats are stale.
EXPLAIN without ANALYZE tells you what the planner would do, not necessarily what it does under load (constant propagation and subquery caching can change plans in ways EXPLAIN doesn't reveal).
For complex multi-join plans, reading the output column-by-column still takes training — layer with concepts/mysql-access-type understanding and Savannah Longoria's explain-reading guide.

Known uses¶

sources/2026-04-21-planetscale-why-isnt-mysql-using-my-index — Francis's eight-reason diagnostic taxonomy all starts from EXPLAIN readings.
sources/2026-04-21-planetscale-how-to-read-mysql-explains — Longoria's canonical EXPLAIN-output reference.
sources/2026-04-21-planetscale-using-redundant-conditions-to-unlock-indexes-in-mysql — Francis uses before/after EXPLAIN pairs to prove redundant-condition de-obfuscation works.

patterns/explain-before-execute-validation — pattern for systematically running EXPLAIN before every production-bound query rollout.
patterns/composite-index-for-and-predicate — uses EXPLAIN as the before/after validation.
concepts/mysql-explain — the diagnostic surface.
concepts/mysql-access-type — the type column ladder.