Inspect queries with EXPLAIN

Prefix a query with EXPLAIN to see table order, access type, candidate keys, selected key, and estimated rows.

Start with the query the application actually runs:

EXPLAIN
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Read the plan as a prediction of how MySQL will find the rows. Useful fields include type, the possible and selected keys, estimated rows, filtered percentage, and Extra. A full scan is not automatically wrong—a small table may be cheaper to scan—but a large estimate for a selective lookup deserves attention.

This query suggests a compound index:

CREATE INDEX orders_customer_created_idx
ON orders(customer_id, created_at);

Column order follows the access pattern. MySQL can locate one customer’s index range and read it in date order. The same index is less helpful for a query that only filters by created_at, because customer_id is its leading column.

Run EXPLAIN again after adding the index. Check that MySQL selected it and that the estimated work improved. Then measure the real query under representative data; optimizer estimates can be wrong, and a plan change is not the same as a user-visible speedup. EXPLAIN ANALYZE can execute the statement and report actual timing and row counts, so use it carefully on statements with production cost.

Every index consumes storage and adds work to inserts, deletes, and updates of indexed columns. Overlapping indexes can be redundant. Add one for a measured read path, confirm the benefit, and keep the smallest index that supports it.

Try it: compare an index on (customer_id) with one on (customer_id, created_at) for the query above. Inspect both the plan and execution time before deciding which to keep.