EXPLAIN plans introduction

EXPLAIN shows how the database plans to run your query—indexes used, join order, estimated rows. Essential when queries slow down in production.

Basic EXPLAIN

EXPLAIN QUERY PLAN
SELECT *
FROM orders
WHERE customer_id = 1;

Practice: Advanced features vary by engine—SQLite 3.25+ supports window functions; test your version with a simple query.

SQLite uses EXPLAIN QUERY PLAN; Postgres uses EXPLAIN (ANALYZE, BUFFERS)—syntax varies by dialect.

Reading scans vs seeks

Table scan reads every row—acceptable on small tables, costly on millions. Index seek jumps to matching keys—goal for selective filters and join keys.

Optimization workflow

-- 1. Run EXPLAIN on slow query
-- 2. Look for full scans on large tables
-- 3. Add or adjust indexes
-- 4. Rewrite JOIN order or simplify OR conditions
-- 5. Re-measure with EXPLAIN ANALYZE where available

Pair with indexes lesson and dialect-specific guides on PostgreSQL and MySQL.

Important interview questions and answers

1. Q: EXPLAIN guarantees speed?
   A: No—it shows plan; ANALYZE executes and measures actual times in supporting engines.
2. Q: First optimization step?
   A: Identify selective WHERE/JOIN columns and whether indexes exist.

Self-check

1. What does a full table scan mean?
2. Why run EXPLAIN before adding random indexes?

Tip: Run EXPLAIN before adding indexes blindly—confirm the planner would use them.