Query Optimization Basics

Reading EXPLAIN Output

EXPLAIN ANALYZE SELECT ...;

Look for:

Indexes help WHERE, JOIN ON, and ORDER BY
B-tree indexes (default) work well for =, <, >, BETWEEN, LIKE 'prefix%'
Composite indexes rely on leftmost column order
Indexes speed reads but add write overhead

Problem	Fix
`SELECT *`	Select only needed columns
Function on indexed column in `WHERE`	Rewrite into range predicates
`NOT IN` with `NULL`s	Prefer `NOT EXISTS`
Correlated subquery per-row	Rewrite as join or window function
`DISTINCT` on large output	Usually indicates incorrect join logic
`UNION` when dedup not needed	Use `UNION ALL`