MySQL Index Design — Field Notes from the Query Plan

An index is not a sticker we attach to a slow query. It is a physical access path with a write cost, a memory footprint and an ordering model.

Start from the access pattern

Consider a query that finds the latest paid orders for one customer:

SELECT id, total_amount, paid_at
FROM orders
WHERE customer_id = ?
  AND status = 'PAID'
ORDER BY paid_at DESC
LIMIT 20;

A useful candidate is:

1 2	CREATE INDEX idx_orders_customer_status_paid ON orders (customer_id, status, paid_at DESC);

The equality predicates lead, followed by the column that provides ordering. But the index is only a hypothesis until the query plan and production distribution confirm it.

Read the plan as evidence

Pay attention to estimated rows, the chosen access type, extra sorting and whether the optimizer’s assumptions match real cardinality. A clean-looking EXPLAIN can still hide skewed data and p99 pain.

The durable habit is simple: measure the query, understand the access path, change one thing, measure again.