Indexes and Performance

What an index actually does#

An index is an auxiliary, sorted data structure — a B-tree in InnoDB — that lets MySQL locate matching rows without scanning the entire table. Without one, WHERE email = 'x@example.com' against a million-row table means reading all one million rows to find matches. With an index on email, MySQL performs something closer to a binary search: a handful of comparisons instead of a million.

CREATE INDEX idx_users_email ON users (email);

InnoDB automatically indexes the primary key, and any column declared UNIQUE. Everything else needs an explicit index if it's queried often.

EXPLAIN — reading a query plan#

EXPLAIN shows how MySQL intends to execute a query, without actually running it — the single most useful diagnostic tool for "why is this query slow."

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

Before an index on user_id, the output looks roughly like this:

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 9482 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

type: ALL means a full table scan — MySQL reads all 9,482 rows and filters afterward. Now add the index and check again:

CREATE INDEX idx_orders_user_id ON orders (user_id);

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

+----+-------------+--------+------+---------------------+---------------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys       | key                 | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------------+---------------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_orders_user_id  | idx_orders_user_id  | 4       | const |    3 | NULL  |
+----+-------------+--------+------+---------------------+---------------------+---------+-------+------+-------+

type: ref means MySQL used the index to jump straight to matching rows, and rows: 3 means it only had to examine 3 rows instead of scanning all 9,482. The key columns worth checking every time:

• type — access method, roughly worst to best: ALL (full scan) → index (full index scan) → range → ref → eq_ref → const. Anything landing on ALL for a large table on a selective filter is worth investigating.
• key — which index MySQL actually chose to use (or NULL if none).
• rows — MySQL's estimate of how many rows it will examine — not necessarily how many it returns.
• Extra — flags like Using filesort (an extra sort step MySQL couldn't avoid) or Using temporary (a temporary table was needed), both worth investigating on a slow query.

EXPLAIN ANALYZE (available since MySQL 8.0.18) goes further and actually runs the query, reporting real timing alongside the estimated plan — more accurate, at the cost of actually executing the query.

Composite indexes and column order#

A composite (multi-column) index is built as a single B-tree over multiple columns, sorted first by the first column, then by the second within ties, and so on — column order determines which queries can use it.

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

This index can efficiently serve:

-- Uses the full index (both columns)
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

-- Uses the index too (leftmost prefix: just user_id)
SELECT * FROM orders WHERE user_id = 42;

But it cannot be used to efficiently serve a query that filters on status alone:

-- Cannot use idx_orders_user_status — status isn't the leftmost column
SELECT * FROM orders WHERE status = 'shipped';

This is the leftmost prefix rule: a composite index on (a, b) can serve queries filtering on a alone, or a and b together, but not b alone — because the B-tree is sorted by a first, so without a value for a, MySQL has no efficient way to jump to matching b values. If both query patterns matter equally, either create a second index on status alone, or order the composite index by whichever column is filtered more selectively and more often, and accept that the less common access pattern falls back to a full scan (or add both).

Rule of thumb for ordering composite index columns: put the column used in equality filters (=) before columns used in range filters (>, <, BETWEEN) — an index can use a range condition to narrow down rows, but everything after the first range condition in the column order stops helping.

-- Good: equality column first, range column second
CREATE INDEX idx_orders_status_date ON orders (status, order_date);

SELECT * FROM orders
WHERE status = 'shipped' AND order_date > '2026-01-01';

What to index, and the cost of over-indexing#

Good index candidates: primary keys and foreign keys (user_id, product_id), columns in frequent WHERE clauses, and columns used in ORDER BY or JOIN conditions.

Every index has a cost, though: it must be updated on every INSERT, UPDATE, and DELETE that touches its columns, and it consumes disk space. A table with ten indexes on rarely-queried columns pays that write cost on every single insert for no read benefit — index only what queries actually use, and periodically check for indexes MySQL never uses in practice (sys.schema_unused_indexes in MySQL 8, a system view built exactly for this).

Common mistakes#

• Adding an index to a column and expecting every query touching that column to speed up — a composite index's leftmost-prefix rule means a query filtering only on the second column of (user_id, status) gets no benefit from that index at all.
• Creating a separate single-column index on every column "just in case," bloating write latency and storage without meaningfully helping the actual query patterns.
• Never running EXPLAIN on a slow query and instead guessing at the fix — EXPLAIN takes seconds and tells you definitively whether an index is being used at all.
• Indexing a low-cardinality column alone (e.g., a status column with only three possible values) expecting a big win — an index barely helps when a third of the table matches any given value; it's more useful as the second column in a composite index alongside a more selective column.