mysql-indexing

---
name: mysql-indexing
description: >-
license: MIT
---

<!-- generated: do not edit — source: knowledge/atoms/databases/mysql-indexing/ -->
<!-- schema: atomVersion=3 skillVersion=2 graphVersion=1 -->
<!-- compile: @php-skills/compiler v1.0.0 (deterministic build) -->

# MySQL Indexing

## When to use

- The user needs a multi-column (composite) or covering index for a query that
filters, joins, or sorts on several columns at once.
- The user wants to run EXPLAIN (or EXPLAIN ANALYZE) and interpret the plan to
understand which index MySQL chose, or why it fell back to a full scan.
- The user has a slow SELECT, JOIN, or ORDER BY that scans a large table and
wants to add or fix an index so the database reads far fewer rows.

## When NOT to use

- Do not apply these rules verbatim to PostgreSQL, SQLite, or a NoSQL store —
their optimizers, index types, and covering semantics differ.
- Do not reach for new indexes when the table holds only a few hundred rows, or
when the workload is overwhelmingly writes with rare reads.

## Core guidance

- **Read the EXPLAIN type column before trusting an index** — Run EXPLAIN and read the `type` column: aim for `const`, `eq_ref`, `ref`, or
`range`. Treat `ALL` (full scan) and a huge `rows` estimate as a missing or
unusable index, and confirm `key` names the index you intended.
- **Order composite columns by the leftmost-prefix rule** — In a composite index, put equality-filtered columns first, then one range or
sort column last. A composite index on `(a, b, c)` only helps queries that use
a leftmost prefix — `a`, or `a, b`, or `a, b, c` — never `b` alone.

## Quick recipe

1. Run `EXPLAIN` (or `EXPLAIN ANALYZE` on MySQL 8) on the slow query and note the
`type`, `key`, and `rows` columns to see whether a full scan is happening. (verify: `type` is `ALL` and `rows` is large, confirming the query lacks a usable index.)
2. Build a composite index listing equality columns first, then the range or sort
column, and include selected columns to make it covering where cheap:
`CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at)`. (verify: The index columns match the query's WHERE, JOIN, and ORDER BY usage in leftmost-prefix order.)
3. Re-run EXPLAIN to confirm `type` improved to `ref`/`range` and `key` names the
new index, then time the query to verify a real latency win before shipping. (verify: EXPLAIN reports the new index in `key`, `rows` dropped sharply, and wall-clock time fell.)

## Best practices

- When a hot query selects only a few columns, add every selected and filtered
column to one index so the query is answered entirely from the index
- For long VARCHAR/TEXT columns, index a leading prefix (`INDEX (url(191))`)
instead of the whole value, choosing a length that keeps selectivity high
while staying inside the index key-length limit
- Write predicates so the indexed column stands alone on one side: prefer
`WHERE created_at >= '2026-01-01'` over `WHERE YEAR(created_at) = 2026`, and
avoid wrapping the column in a function or a leading-wildcard `LIKE '%foo'`
- Index columns whose values are highly selective (many distinct values, like
`email` or `user_id`)

## Anti-patterns

| Anti-pattern | Why it fails | Do instead |
| --- | --- | --- |
| Hard-coding FORCE INDEX to paper over a bad plan | The optimizer usually chooses well once statistics are fresh; a forced hint hides the real cause and breaks later. | Refresh statistics with `ANALYZE TABLE`, fix the index design or the predicate,<br>and reserve `FORCE INDEX` for a proven, documented optimizer mis-estimate. |
| Adding an index to every column | Over-indexing trades write throughput and disk for indexes the query planner never chooses. | Index only columns that appear in real WHERE, JOIN, and ORDER BY clauses, and<br>prefer composite indexes that serve several queries over many single-column ones. |
| SELECT * defeats covering indexes | A covering index only helps when the selected columns all live in the index; `*` guarantees they do not. | Select the explicit column list the code uses so a covering index can answer<br>the query, and let EXPLAIN confirm `Using index`. |

See [references/anti-patterns.md](references/anti-patterns.md).

## Common mistakes

1. **Wrapping the indexed column in a function kills the index** — A predicate like `WHERE DATE(created_at) = '2026-07-02'` or
`WHERE LOWER(email) = ?` makes the query non-sargable, so MySQL scans every row
even though `created_at` or `email` is indexed. Fix: Rewrite as a sargable range (`created_at >= '2026-07-02' AND created_at < '2026-07-03'`),
store a normalized column, or add a functional/generated-column index on the expression.
2. **Type mismatch causes a silent implicit cast and full scan** — Comparing an indexed numeric column to a quoted string (`WHERE user_id = '42'`)
or a VARCHAR column to a number forces an implicit type conversion that
disables the index. Fix: Bind parameters with the correct PHP type so the driver sends a matching SQL
type, and align column types across joined tables (both INT, or both VARCHAR
with the same collation).
3. **Redundant and duplicate indexes slow writes** — Keeping an index on `(a)` alongside `(a, b)` is redundant — the composite
already covers `a` — and every duplicate index adds write overhead and storage
with no read benefit. Fix: Drop the redundant single-column index, audit with a tool such as
pt-duplicate-key-checker, and keep only indexes that a real query plan uses.
4. **Composite index in the wrong column order is dead weight** — An index on `(status, created_at)` does nothing for `WHERE created_at > ?` with
no `status` filter, because the query does not use the leftmost prefix. Fix: Order composite columns by usage — equality predicates first, then the range or
sort column — and add a separate index for queries that filter only on the
trailing column.

## Version notes

- **EXPLAIN ANALYZE and invisible indexes arrived in MySQL 8** (PHP >=7.4) — MySQL 8.0 adds `EXPLAIN ANALYZE` (real timed execution), descending indexes,
functional indexes, and invisible indexes for safe rollout; MySQL 5.7 has only
the estimate-based `EXPLAIN`.

## Security

- **Index-driven queries still need prepared statements** — Never interpolate user input into WHERE clauses while tuning indexes — bind the
filter value as a parameter. Column and index names cannot be bound, so
validate any dynamic ORDER BY column against a fixed allow-list.

## Testing

- Activation and decision behavior are verified in `eval/scenarios/databases/mysql-indexing.yaml`.

## Additional resources

- [references/anti-patterns.md](references/anti-patterns.md)
- [references/core.md](references/core.md)
- [references/examples.md](references/examples.md)
- [references/pitfalls.md](references/pitfalls.md)
- [references/recipes.md](references/recipes.md)
- [references/version-differences.md](references/version-differences.md)

## Knowledge graph

**Related:** [databases/connection-pooling](../../databases/connection-pooling/SKILL.md), [databases/transactions](../../databases/transactions/SKILL.md), [security/sql-injection-prevention](../../security/sql-injection-prevention/SKILL.md)  

<!-- graph-fragment: graph/fragments/databases/mysql-indexing.yaml -->