Skip to content

Database workshop · 7 chapters · 91 minutes

Make the slow query fast.

Most slow pages are a query that reads 200,000 rows to show 20. Learn to read a query plan, choose the index that fixes it, kill N+1 queries, page without OFFSET, and guard it all in CI.

  • SQLite, built into Python: nothing to install or run.
  • A real shop database, 200,000 orders, rebuilt the same every time.
  • A bench that shows time, statements and the plan for every query.
  • The ideas carry straight over to Postgres and MySQL.
Start the workshopView the code
python -m shop benchmilliseconds
query no index indexed
recent_orders 4.23 0.01
revenue_since 8.36 0.09
report 232.01 7.67
page_after 11.66 0.02
before SCAN orders
after SEARCH orders USING INDEX

What you work with

Planslab/shop/plan.pyRead EXPLAIN QUERY PLAN and spot the scan.
Indexeslab/indexes.sqlComposite, column order, covering.
Querieslab/shop/queries.pyN+1 against JOIN, OFFSET against keyset.
Guardlab/shop/bench.pyFail CI when a plan regresses.

7 short chapters

Each one is a few minutes of reading, one change to make, and a number that proves it worked. By the end you can open any slow endpoint and find out why.

  1. 01Why queries get slow10 min
  2. 02Read the plan12 min
  3. 03Your first index15 min
  4. 04Column order and covering indexes15 min
  5. 05N+1 queries15 min
  6. 06Pagination that stays fast12 min
  7. 07Guard it in CI12 min

Run it in a minute

You need uv and git. uv installs Python for you.

git clone https://github.com/jars-demo/slow-query-demo.git
cd slow-query-demo/lab
uv sync
uv run python -m shop bench