EXPLAIN (and EXPLAIN ANALYZE in 8.0.18+) reveals access type, key used, and estimated rows.
EXPLAIN output
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
-- Look at: type, key, rows, Extra (Using filesort, Using temporary)Practice: Run read-only EXPLAIN on practice tables where possible.
Fixing ALL scan
Add composite index matching WHERE + ORDER BY columns when type=ALL on large tables.
Slow query log
Enable slow_query_log in staging to catch queries exceeding threshold—pair with EXPLAIN.
Important interview questions and answers
- Q: Using filesort?
A: Sort could not use index—consider index on ORDER BY columns. - Q: Covering index?
A: Extra may show Using index when all columns served from index.
Self-check
- What does type=ALL mean?
- Name two EXPLAIN columns to read first.
Pitfall: EXPLAIN on DELETE still plans delete—use transactions in tests.
Interview prep
- Using filesort?
Sort may not use index—tune index or query.
- EXPLAIN ANALYZE?
8.0.18+ executes and shows actual timings.