Skip to content

7 chapters · 91 minutes

The workshop

From a bench full of full table scans to indexes you can defend and a CI check that keeps them. Work through them in order; each ends with a way to check it worked.

  1. 01Why queries get slowBuild a small shop database, time every query its pages run, and find out which ones read far more rows than they return.10 min
  2. 02Read the planBefore you add an index, ask the database what it intends to do. EXPLAIN QUERY PLAN tells you in one line per step.12 min
  3. 03Your first indexAdd one index, watch a scan become a search, then add a second column and watch the sort disappear too.15 min
  4. 04Column order and covering indexesSame two columns, opposite order, very different speed. Then add one more column so the table is never read at all.15 min
  5. 05N+1 queriesOne query for the list, then one more for every row. Fast on your laptop, slow in production. Find it by counting statements, and fix it with one JOIN.15 min
  6. 06Pagination that stays fastPage 1 is fast. Page 5,000 is not, because OFFSET reads every row it skips. Keyset pagination remembers where you stopped instead.12 min
  7. 07Guard it in CIA fast query is one careless migration away from slow again. Check query plans on every pull request, so a dropped index fails the build instead of the pager.12 min