MySQL Index: How It Works and When to Create One

A MySQL index is a data structure (usually a B-tree) that lets the database find rows without scanning the whole table. Without an index, a WHERE or JOIN query can go from milliseconds to seconds as data grows.

When an index helps

Query	Relevant index
`WHERE email = ?`	Index on `email`
`WHERE status = ? ORDER BY created_at`	Composite `(status, created_at)`
`JOIN orders ON orders.user_id = users.id`	Index on `orders.user_id`
`WHERE slug = ?`	Unique index on `slug`

Composite indexes and column order

An index on (status, created_at) can serve a status filter alone, or status plus created_at ordering. It cannot serve created_at alone. Column order follows your query pattern, not just a list of columns.

How to check

Use EXPLAIN before and after adding an index:

EXPLAIN SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC;

Look at type, key, and rows. ALL means a full scan; ref or range is usually better.

What to watch for

Indexes slow writes. Every INSERT, UPDATE, and DELETE must update the index.
Do not index every column. Choose the ones used in filters, joins, and ordering.
Avoid functions on indexed columns. WHERE YEAR(created_at) = 2026 often skips the index.
Low cardinality is weak. A column with few unique values is rarely useful on its own.
Back up before changing the schema. Test on staging, then measure the query after the change.

Measure with a query monitor before and after the change.

FAQ

Should every column have an index?

No. An index no query uses only adds write cost and disk space.

What is a composite index?

An index with several columns, such as (user_id, created_at). Column order decides which queries can use it.

Why is the query still slow after adding an index?

The column order may not match, a function may wrap the column, or the optimizer may choose a full scan because the table is small.