Phase 7 of 8 · 4 lessons
Performance: indexes and query plans
Know why a query is slow, read the plan the database chose, and write queries and indexes that let it find rows without reading the whole table.
On six small tables everything is instant. On a hundred million rows, the difference between reading every row and jumping straight to the right ones is seconds versus milliseconds. Interviewers for backend and data roles always ask how you would make a slow query fast.
Lessons, in order
-
7.1
Indexes: how a B-tree makes lookups fast
An index is a separate, sorted copy of one or more columns — usually a B-tree — with pointers back to the rows, so the database can jump to matching rows instead of scanning the whole table.
-
7.2
Reading a query plan with EXPLAIN
EXPLAIN shows the plan the database chose for a query — which tables it scans, which indexes it searches, how it joins and sorts — without (or, with ANALYZE, while) running it.
-
7.3
Index-friendly queries: sargable filters and composite indexes
A filter is sargable when the database can use an index for it — the indexed column stands alone on one side of the comparison — and a composite index on (a, b) helps queries that filter on a, or on a and b, but not on b alone.
-
7.4
How a database runs a join: nested loop, hash join, merge join
Databases use three main join methods — a nested loop (for each row of one table, look up matches in the other), a hash join (build a hash table of one side, probe it with the other), and a merge join (walk two sorted inputs together) — and the optimiser picks one per join.