B-tree indexes speed WHERE/JOIN/ORDER BY. EXPLAIN shows whether the optimizer uses them.
Create indexes
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status_created ON orders (status, created_at);
SHOW INDEX FROM orders;Practice: Run on practice.
Covering index idea
If index contains all selected columns, optimizer may avoid table lookups—add columns only when hot queries benefit.
EXPLAIN snippet
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';Important interview questions and answers
- Q: type=ALL in EXPLAIN?
A: Full table scan—often needs index on filter columns. - Q: Too many indexes?
A: Slows writes and migration time—index for real queries.
Self-check
- Create index on customer_id.
- What does EXPLAIN help you see?
Tip: Composite index column order matches WHERE then ORDER BY.
Interview prep
- type=ALL?
Full table scan—often needs better index.
- Composite order?
Match equality filters then sort columns.