What an index does

An index helps MySQL locate rows without scanning every row. It consumes storage and adds work to inserts, updates and deletes.

Create a composite index

CREATE INDEX idx_orders_customer_created

ON orders (customer_id, created_at);

Composite indexes are ordered by their leftmost column. Choose order based on real query patterns.

Inspect a query

EXPLAIN

SELECT id, total

FROM orders

WHERE customer_id = 42

ORDER BY created_at DESC

LIMIT 20;

Review the chosen key, access type, estimated rows and extra operations.

Tuning workflow

1. Identify a slow query using measurements.

2. Capture SQL and representative parameters.

3. Run EXPLAIN (and EXPLAIN ANALYZE where supported and safe).

4. Review indexes and filtering.

5. Change one thing and benchmark again.

6. Monitor write latency and index size.

Interview practice

Explain a composite index, leftmost-prefix principle, covering index and why indexing every column is usually a poor strategy.