Indexes are the cheapest performance win in MySQL and the most commonly botched. These are the rules we apply on schema audits — none of them are exotic, but applied together they routinely remove the majority of a workload's read cost.
Rule 1: design for the query, not the column
An index is not "the column people search on"; it is a sorted structure that must match the shape of a query. For:
SELECT ... WHERE tenant_id = ? AND state = 'active'
ORDER BY updated_at DESC LIMIT 50;
the right index is (tenant_id, state, updated_at) — equality columns first, in any order, then the range/sort column last. (updated_at, tenant_id) looks similar and is nearly useless here: the leading column is a range, so the equality filters cannot use the B-tree.
The leftmost-prefix rule is the whole game: an index on (a, b, c) serves queries filtering on a, on a, b, and on a, b, c — but not on b or on b, c alone.
Rule 2: one selective column beats three weak ones
Selectivity is the fraction of rows a value narrows to. Indexing a status column with four values across 100M rows buys almost nothing on its own; the optimizer may rightly ignore it. Check real selectivity before adding an index:
SELECT COUNT(DISTINCT col) / COUNT(*) FROM t;
Low-selectivity columns still earn their place inside a composite index (as in Rule 1), where they subdivide an already-narrow slice. They rarely deserve a standalone index.
Rule 3: make hot queries covering
If an index contains every column a query touches, InnoDB answers from the index alone and never visits the clustered row — Using index in EXPLAIN. For a query running thousands of times a second, appending one or two selected columns to an existing index is often the difference between a working set that fits in the buffer pool and one that does not. Weigh it consciously: every added column makes writes slightly more expensive.
Rule 4: respect the clustered index
InnoDB stores the table as the primary key; every secondary index entry carries the primary key as its pointer. Two consequences:
- A fat primary key (a 36-byte UUID string, say) bloats every secondary index on the table. If you need UUIDs, store them as
BINARY(16)— and on 8.0,UUID_TO_BIN(uuid, 1)reorders the timestamp bits so inserts stay roughly sequential instead of scattering across pages. - Range scans on the primary key are the fastest access path in the engine. Choosing a primary key that matches your dominant access pattern (
(tenant_id, id)on a strictly tenant-scoped table, for instance) can eliminate whole classes of secondary indexes — at the cost of less convenient single-row lookups, so make the trade deliberately.
Rule 5: nothing kills an index like a function
Any expression wrapped around an indexed column defeats it:
WHERE DATE(created_at) = '2026-05-01' -- full scan
WHERE created_at >= '2026-05-01'
AND created_at < '2026-05-02' -- range scan
The same applies to implicit conversions — comparing a VARCHAR column to a numeric literal, or joining columns with different collations (a common leftover after partial utf8mb4 migrations). When the expression is genuinely necessary, 8.0 supports functional indexes: INDEX ((LOWER(email))).
Rule 6: prune as deliberately as you add
Every index taxes every write and competes for buffer-pool space. Real tables accumulate redundant indexes for years — (a) alongside (a, b), or near-duplicates left by ORM migrations. Find the dead ones:
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;
Unused-index data resets at server restart, so judge it over a representative window that includes month-end or seasonal jobs. On 8.0 you can soft-delete first — ALTER TABLE t ALTER INDEX i INVISIBLE; — watch for a week, and drop only when nothing regresses. That single feature has taken most of the fear out of index cleanup.
Rule 7: measure, then keep the receipt
Before an index change: capture the query's digest latency and rows-examined from the Performance Schema. After: capture again. "Feels faster" is not a result; a before/after on rows_examined_avg is. Keeping those receipts also protects the change from being reverted by the next person who assumes the index is dispensable.
None of these rules require new technology — just treating indexes as designed artifacts with owners and evidence, rather than sediment that accumulates under the application.