How do you read an SQL execution plan? | SQL Interview Questions

What You'll Learn

Using EXPLAIN
Reading the output
Identifying bottlenecks

EXPLAIN Basics

query.sqlSQL

-- MySQL
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

MySQL EXPLAIN Output

query.sqlSQL

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1  | SIMPLE      | users | ref  | idx_email     | idx_email | 767 | const | 1    | Using index |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

Key Fields

type (Access Type)

From best to worst:

system: Single row
const: Primary key lookup
ref: Index lookup
range: Index range scan
index: Full index scan
ALL: Full table scan (avoid!)

key

Index being used (NULL = no index)

rows

Estimated rows to examine

Extra

Using index: Index-only scan (good)
Using where: WHERE filtering
Using filesort: Sorting without index (can be slow)
Using temporary: Temp table needed (can be slow)

Identifying Issues

query.sqlSQL

-- Bad: Full table scan
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- type: ALL, rows: 1000000

-- Good: Index scan
EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01';
-- type: range, rows: 50000

PostgreSQL EXPLAIN ANALYZE

query.sqlSQL

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'john@example.com';

-- Output includes:
-- Actual time (planning + execution)
-- Actual rows vs estimated
-- Buffer usage (memory/disk reads)