Skip to content

PATTERN Cited by 2 sources

EXPLAIN-driven index verification

Always run EXPLAIN on the query to confirm the index is actually being used — creating an index and hoping is not a verification. This pattern is the minimum-viable discipline for index design: every new index is a hypothesis that the planner will pick it up; EXPLAIN is the test that proves the hypothesis.

Canonical framing

Justin Gage names the verification discipline directly:

This way of using EXPLAIN can also be useful for debugging when creating an index, i.e. verifying that a new one is working as intended.

— sources/2026-04-21-planetscale-how-do-database-indexes-work

The two EXPLAIN output columns that matter for verification:

Column Meaning
possible_keys Indexes the planner considered for this query
key Index the planner actually chose

A new index is only effective if it appears in key for the target query — appearing in possible_keys but not key means the planner considered and rejected it (see concepts/index-selectivity for the standard rejection cause).

The workflow

-- 1. Baseline: EXPLAIN before the index
EXPLAIN SELECT * FROM users WHERE most_recent_activity > NOW() - INTERVAL 1 DAY;
-- type: ALL, rows: 300000, key: NULL

-- 2. Create the index
CREATE INDEX activity_idx ON users(most_recent_activity);

-- 3. EXPLAIN after the index — verify
EXPLAIN SELECT * FROM users WHERE most_recent_activity > NOW() - INTERVAL 1 DAY;
-- type: range, rows: 1500, key: activity_idx   ← verification success

The canonical before/after signals:

  • type moves from ALL (full table scan) to range / ref / const (see concepts/mysql-access-type)
  • rows drops sharply
  • key names the new index
  • key_len (for composite indexes) reveals how many columns are actually in use

When verification fails

If the new index appears in possible_keys but not key, the planner found it but rejected it. Aaron Francis's 2023 post (sources/2026-04-21-planetscale-why-isnt-mysql-using-my-index) enumerates eight reasons this happens:

  • Considered-but-not-chosen: low selectivity, stale statistics, table-scan is cheaper
  • Not-considered-at-all: leading wildcard, leftmost- prefix miss, type / charset mismatch, index obfuscation, invisibility

Each failure mode has a distinct remediation — documented on the companion patterns/explain-for-index-verification pattern, which canonicalises the verify-first-before- diagnose discipline.

Why the pattern matters

Three failure modes this discipline catches:

  1. Silent plan regression after a schema change. A migration that alters a column's type or collation can invalidate an existing index's use (type-mismatch non-sargable class). EXPLAIN surfaces this as key: NULL where previously key: idx_x.
  2. Composite-index partial utilisation. A composite index on (a, b, c) may only be partially used (just the leading a column). key_len is the only signal — smaller value than expected means not all columns are engaged.
  3. Query-pattern drift. The query shape the index was designed for (e.g. WHERE email = 'x') may have migrated to something the index doesn't serve (e.g. WHERE LOWER(email) = LOWER('x') — function on indexed column = index obfuscation). EXPLAIN catches the regression immediately.

Standalone EXPLAIN vs EXPLAIN ANALYZE

Plain EXPLAIN returns the planner's estimate — based on statistics, without running the query. EXPLAIN ANALYZE (MySQL 8.0.18+, Postgres since forever) actually runs the query and reports real row counts and timings. For index verification, plain EXPLAIN is usually sufficient — the planner's intent is what you're verifying. Use EXPLAIN ANALYZE when estimated rows and actual rows diverge (stale statistics, skewed data — see concepts/mysql-index-statistics).

Engine parity

Engine Command Notes
MySQL 8.0+ EXPLAIN, EXPLAIN ANALYZE Canonical output schema with possible_keys / key
Postgres EXPLAIN, EXPLAIN ANALYZE Different output shape — planner nodes (Index Scan / Seq Scan) name the chosen index inline
SQL Server SET SHOWPLAN_ALL ON / graphical plan Execution-plan pane in SSMS

Seen in

Last updated · 550 distilled / 1,221 read