Indexes

InnoDB indexes are B+ trees. The primary key is the clustered index — table rows live in PK order. Secondary indexes leaf nodes store PK values, causing a double lookup when columns are not covered.

					-- Left-prefix rule: index (a, b, c) helps WHERE a=? AND b>? but not WHERE b=? alone
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status, created_at);

-- Covering index avoids table lookup
CREATE INDEX idx_orders_cover ON orders (customer_id, status, total);

EXPLAIN SELECT total FROM orders
WHERE customer_id = 10 AND status = 'paid';
-- possible: Using index
				

Design PK as narrow and insert-friendly as business rules allow (auto-increment or time-ordered UUID strategies). Random UUID PKs fragment the clustered index. Use `EXPLAIN` and `SHOW INDEX` to validate cardinality and index usage.

On interviews: explain clustered vs secondary indexes, left-prefix rule, covering indexes, and why primary key choice affects write amplification.

Common pitfalls: wide composite indexes that never match query prefixes; redundant indexes; UUID v4 as PK on huge tables without awareness of fragmentation; ignoring `Using filesort` in EXPLAIN.

The trade-off is faster reads via covering indexes versus larger indexes and slower inserts/updates on the clustered key.

Checklist:

• Align composite index order with WHERE and ORDER BY.
• Prefer covering indexes for hot read paths.
• Keep clustered PK monotonic when possible.
• Remove unused indexes after slow-query review.