Skip to content

PLANETSCALE 2023-05-04 Tier 3

Read original ↗

PlanetScale — Why isn't MySQL using my index?

Aaron Francis (PlanetScale, originally 2023-05-04, re-fetched 2026-04-21) publishes the canonical wiki taxonomy of the eight reasons MySQL silently ignores an index you expected it to use. The post is a field manual not a deep-dive: each reason is walked with a CREATE TABLE people schema, a worked EXPLAIN transcript showing the degraded plan, and the one-line fix. It is the companion diagnostic post to Francis's own Using redundant conditions to unlock indexes in MySQL (which attacks one specific class — index obfuscation) and to Savannah Longoria's How to read MySQL EXPLAINs (which canonicalises the EXPLAIN output format this post reads to diagnose every case).

Summary

Before asking why your index isn't being used, Francis asks you to verify that it isn't via EXPLAIN's possible_keys + key columns:

Our index on first_name was considered, and it was chosen. These are separate pieces of information, both of which are valuable! Before your index can be chosen, it must first be considered.

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

From there, the post splits the reasons into two phases: (A) considered but not chosen (the optimiser saw the index, evaluated alternatives, picked a different one or picked the table scan) and (B) not considered at all (the optimiser structurally could not evaluate your index as an option).

Key takeaways

  1. EXPLAIN is the starting diagnostic, not the answer. possible_keys shows indexes the optimiser considered; key shows the one actually used. "Before your index can be chosen, it must first be considered." If your index is in possible_keys but not key, phase A; if not in possible_keys at all, phase B. (Source: Why isn't MySQL using my index?)

  2. The optimiser picks the most selective index by default. Selectivity (COUNT(DISTINCT col) / COUNT(*)) measures how unique a column's values are — higher is better for filtering. Francis's worked numbers on a 491,583-row people table: first_name = 0.0060, state = 0.0000 (rounds to zero), id = 1.0000. When multiple indexes could satisfy a query, MySQL picks the higher-selectivity one: "the optimizer has decided that the first_name index is the best choice because the first_name index is more selective." (Source: Why isn't MySQL using my index?)

  3. Cardinality and selectivity differ. Cardinality is a count of distinct values (3,009 distinct first_name values); selectivity is a ratio (cardinality / total rows). Cardinality alone can mislead without the table-size denominator. All unique indexes are perfectly selective (= 1.0000) by definition. (Source: Why isn't MySQL using my index?)

  4. Average selectivity misleads on skewed data. A type column with 99% "user" + 1% "admin" has poor average selectivity but is highly selective for admin queries. "When you're querying for admins, it is highly selective. So while checking average selectivity is a good rule of thumb, pay careful attention to unevenly distributed data." (Source: Why isn't MySQL using my index?)

  5. MySQL keeps sampled statistics, not exact counts. Cardinality in SHOW INDEXES is stored from random sampling, refreshed automatically once "10% of a table has changed" (per InnoDB persistent stats docs). ANALYZE TABLE people; forces immediate refresh when stale stats cause the planner to pick a bad index. (Source: Why isn't MySQL using my index?)

  6. A table scan can be faster than using the index. For small tables or queries returning a large fraction of rows, reading all rows sequentially off disk beats the secondary-index two-step (index walk + clustered-index walk per matched row). "In situations where most of the rows will be fetched, reading all of the rows in order off of the disk is faster than going to the index first." Sometimes type: ALL in EXPLAIN is the optimiser being correct. (Source: Why isn't MySQL using my index?)

  7. Leading-wildcard LIKE disqualifies an index. WHERE name LIKE 'Aa%' uses the index (type: range, 356 rows). WHERE name LIKE '%ron' does not (type: ALL, 493,889 rows). "MySQL can use the index until it reaches the first wildcard character. The index is not considered if the search string starts with a wildcard character." Root cause: a B+tree sorted by prefix cannot answer suffix queries. (concepts/wildcard-prefix-non-sargable.) For robust suffix/substring search, use fulltext indexes. (Source: Why isn't MySQL using my index?)

  8. The leftmost-prefix rule governs composite indexes. With INDEX multi (first_name, state): a query filtering on first_name alone uses it; a query filtering on both uses it; a query filtering on state alone does not. "MySQL will only be able to use the columns starting on the left and working toward the right. It cannot skip any columns." The index is a B+tree keyed on the tuple (first_name, state) — sorted first by first_name, then within each first_name by state — so a state-only predicate cannot seek on it. (Source: Why isn't MySQL using my index?)

  9. Join-column type / charset mismatch disqualifies an index. concepts/join-column-type-mismatch: if the two join columns differ in type+size (VARCHAR(10) vs CHAR(15)) or charset (utf8mb4 vs latin1), the optimiser will not use the index on the joined side. Fix: align column definitions on both sides — "It may, in fact, be beneficial to lengthen the VARCHAR column to match the CHAR and allow the use of an index, even if the data won't be 15 characters long." (Source: Why isn't MySQL using my index?)

  10. Index obfuscation hides the indexed column from the optimiser. Wrapping an indexed column in a function (YEAR(created_at) = 2023) flips the predicate from "look up values of created_at" to "look up values of YEAR(created_at)" — the second index does not exist, so the predicate is not index-eligible. "You've hidden the indexed column, and MySQL cannot see it." Fix: unwrap and use a range scan (created_at BETWEEN '2023-01-01' AND '2023-12-31'). Canonicalised on this wiki as concepts/index-obfuscation (see the companion Using redundant conditions post by the same author for the de-obfuscation escape hatch). (Source: Why isn't MySQL using my index?)

  11. Invisible indexes are a valid no-op cause. ALTER TABLE … ALTER INDEX … INVISIBLE keeps the index physically present and maintained, but hides it from the planner. Useful as a reversible-drop-test primitive; check SHOW INDEXES Visible = NO before blaming other reasons. (Source: Why isn't MySQL using my index?)

  12. Index hints are the last-resort escape hatch. SELECT … USE INDEX (state) WHERE … forces the optimiser's hand. Francis's worked counter-example (forcing state instead of first_name): rows climbs from 180 → 246,944, a 1,370× regression. "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." Pattern: override with caution, re-evaluate as data distribution changes. (Source: Why isn't MySQL using my index?)

  13. Index merge optimisation exists but should not be designed for. Footnote 1 flags that MySQL can combine two index scans for a single query, but: "It's still better to plan your indexes so that only one is used." See patterns/composite-index-for-and-predicate as the preferred alternative. (Source: Why isn't MySQL using my index?)

Operational numbers

  • 491,583-row people table — baseline corpus for all worked examples (reported via SHOW INDEXES Cardinality column).
  • Selectivity ratios: id = 1.0000 (perfect, unique); first_name = 0.0060 (3,009 distinct values / 491,583); state = 0.0000 (rounds to zero; 2 distinct values).
  • Wildcard fix impact: LIKE 'Aa%' → type: range, 356 rows estimated; LIKE '%ron' → type: ALL, 493,889 rows estimated (~1,387× more work per query).
  • Forced-index regression: USE INDEX (state) on the same two-predicate query picked by the optimiser for first_name: rows estimate 180 → 246,944 (~1,370× regression).
  • InnoDB stats refresh trigger: "10% of a table has changed" since last refresh.
  • Manual refresh: ANALYZE TABLE [table] forces immediate recalculation when stale stats mislead the planner.

Extracted entities

Systems

  • systems/mysql — the planner, the EXPLAIN surface, the USE INDEX / FORCE INDEX hint syntax, and the 8.0 invisible-index primitive.
  • systems/innodb — the default storage engine, whose B+tree secondary-index structure explains why leading-wildcard and leftmost-prefix-violations are structurally index-ineligible; whose persistent stats supply the cardinality numbers the planner reasons on.

Concepts (new)

  • concepts/index-selectivity — COUNT(DISTINCT col) / COUNT(*); the ratio the optimiser uses to rank candidate indexes. Root-of-framing definition.
  • concepts/index-cardinality — COUNT(DISTINCT col); the count (not ratio) MySQL samples and stores in SHOW INDEXES. Canonicalises the distinction from selectivity.
  • concepts/mysql-index-statistics — the sampled-at-write-threshold cardinality MySQL keeps in information_schema.STATISTICS / SHOW INDEXES. Canonicalises the 10%-of-table-changed auto-refresh threshold and ANALYZE TABLE as the manual refresh.
  • concepts/leftmost-prefix-rule — the B+tree-sort-order consequence that a composite index on (A, B, C) can answer queries filtering on A, (A, B), (A, B, C) but not B alone, C alone, or (B, C).
  • concepts/wildcard-prefix-non-sargable — leading-wildcard LIKE '%ron' is structurally not index-eligible because a B+tree sorted by prefix cannot seek on suffix.
  • concepts/skewed-column-selectivity — the caveat that average selectivity is misleading on unevenly distributed data; "99% user + 1% admin" worked example.
  • concepts/join-column-type-mismatch — mismatched type / size / charset between joined columns disqualifies the index on the joined side.
  • concepts/mysql-index-hint — USE INDEX, FORCE INDEX, IGNORE INDEX syntax and the data-distribution-drift risk of long-lived hints.

Concepts (extended)

  • concepts/index-obfuscation — Francis's 2023-05-04 post names this post's reason #6; his 2023-06-07 post canonicalises the vocabulary and the de-obfuscation escape hatch. This ingest sharpens the cross-source pairing.
  • concepts/mysql-invisible-index — Francis flags the invisible index as a candidate cause of an index-ignored-by-planner mystery. Extends the page's Seen-in.
  • concepts/secondary-index — the two-step lookup (index walk + clustered walk) and the table-scan-faster-than- index threshold live here.
  • concepts/composite-index — leftmost-prefix rule is the structural property that governs its usability.
  • concepts/mysql-explain — possible_keys vs key distinction; the diagnostic foundation.
  • concepts/mysql-access-type — ref / range / ALL transitions that Francis's EXPLAIN transcripts show.

Patterns (new)

  • patterns/explain-for-index-verification — make EXPLAIN your_query the first step in any "why isn't this query fast?" investigation. Concrete checklist: read possible_keys, read key, read type, read rows.
  • patterns/force-index-escape-hatch — when the optimiser is provably wrong and the cost of the wrong plan justifies the lock-in risk, use USE INDEX (name) or FORCE INDEX (name). Pattern includes the re-evaluation cadence (as data distribution changes, the force may become harmful).

Caveats

  • Pedagogy voice (~2,500 words), no production incident narrative and no field-observed frequency breakdown of which of the eight reasons most commonly trips people up.
  • Index merge optimisation relegated to a footnote without a worked example — the wiki's treatment defers to patterns/composite-index-for-and-predicate as the preferred alternative.
  • Statistics refresh mechanism is elided beyond naming the 10% threshold — InnoDB persistent stats sample count (innodb_stats_persistent_sample_pages), transient alternative, and per-index override syntax not walked.
  • The 67 KB / 256-pages sample default for InnoDB persistent stats (manual) not cited.
  • FORCE INDEX vs USE INDEX distinction not drawn — USE INDEX is a suggestion the planner can ignore in favour of table-scan; FORCE INDEX makes index use effectively mandatory by pricing table-scan out of consideration.
  • Join-column-type-mismatch section cites the manual but doesn't enumerate which cross-type comparisons still index-qualify (e.g. INT vs BIGINT UNSIGNED, implicit-cast rules) — the MySQL manual's "Comparison of Indexed Columns" section is the canonical reference.
  • Index obfuscation treatment is brief — the de-obfuscation escape hatches (sargable rewrite, redundant condition, functional index, generated column) all live on companion Francis posts; this post names the problem without enumerating all fixes.
  • No engagement with cross-DB parity — Postgres has similar (planner picks alternative plans; functional indexes are first-class; partial indexes with skew hints) but this is MySQL-only.
  • 2023-05-04 original; the wiki's sibling posts on redundant conditions (2023-06-07), functional indexes (MySQL 8.0.13), and generated hash columns (2025) layer subsequent mechanism detail on top.

Source

Last updated · 470 distilled / 1,213 read