Reference · Cheat sheet

When indexes are ignored

The one-page compressed essence: an index only seeks on the bare column compared to a matching-type constant.

Lesson: 0004Context: InnoDB, MySQL 8.0+

The principle

Sargable = bare column vs matching constant

A B-tree indexes the raw column values, in order. It can seek only when the query touches that bare column directly with a type-compatible constant. Wrap it, mistype it, or open the wildcard left → full scan.

The four killers & their fixes

KillerExample (ignored)Fix
Function / expression on column YEAR(created_at)=2026 Range rewrite created_at >= … AND < …, or a functional index
Leading wildcard LIKE '%pat' Anchor it: LIKE 'pat%'; else FULLTEXT / reversed-column trick
Implicit type mismatch str_col = 1 Match types: str_col = '1'; align join column types & collations
Low selectivity (not a bug) status='active' (90% rows) Nothing — a scan is genuinely cheaper; or make it covering / composite

LIKE rules

LIKE 'pat%'index used (range)
LIKE '%pat'ignored
LIKE '%pat%'ignored

Sargable rewrites

YEAR(d) = 2026 → d >= '2026-01-01' AND d < '2027-01-01' DATE(ts) = '2026-08-17' → ts >= '2026-08-17' AND ts < '2026-08-18' col + 0 = 5 → col = 5 UPPER(name) = 'X' → functional index ((UPPER(name))) [8.0.13+] str_col = 1 → str_col = '1' LIKE '%term%' → FULLTEXT index + MATCH … AGAINST

Diagnose in EXPLAIN

possible_keys lists it, key = NULL → one of the four killers type: ALL on a big table → confirm which killer, rewrite key used but you expected NULL? → low selectivity made scan cheaper (fine) verify with EXPLAIN ANALYZE → estimated vs actual rows; ANALYZE TABLE if off

Source: MySQL 8.0 Manual — B-Tree/Hash Indexes (LIKE) · Type Conversion · Functional key parts · All lessons