~/learn/SQL · Query performance
Reading a query plan
Ask the engine what it intends to do before you optimise.
why this matters
Guessing why a query is slow wastes hours. The plan tells you exactly what the engine will do before you change anything.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
EXPLAIN QUERY PLAN prints the strategy: which tables are scanned, which indexes are used and in what order tables are joined. Postgres and MySQL use EXPLAIN / EXPLAIN ANALYZE for the same thing.
Try it: Create an index on orders(customer_id) and run the plan again.
The rewrites that pay off most: stop wrapping filtered columns in functions, filter before joining rather than after, select only the columns you need, and replace a correlated subquery that runs per row with a single join or window function.
Note
Measure before and after on realistic data volumes. On tiny tables a scan is often the fastest plan and every optimisation looks pointless.
Two words that matter most
SCAN table ← reads every row (slow on big tables) SEARCH table USING INDEX ← jumps straight there (fast)
Pro tip
Run EXPLAIN QUERY PLAN before and after adding an index so you can prove the change actually helped.
key takeaways
- EXPLAIN QUERY PLAN shows the engine's intent, and costs nothing to run.
- SCAN means a full read; SEARCH USING INDEX means a targeted lookup.
- Measure, change one thing, then measure again.
check your understanding
Your plan says SCAN on a 5-million-row table you filter by user_id. What is the likely fix?
free practice
Build confidence with a question you can solve without Pro.