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

See 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/core.md
• references/examples.md
• references/pitfalls.md
• references/recipes.md
• references/version-differences.md

Knowledge graph

Related: databases/connection-pooling, databases/transactions, security/sql-injection-prevention