learning-it by example

Back to the video

How Database Indexes Work: B-tree vs Full Scan in SQLite

200,000 rows: one CREATE INDEX turned a full scan into a search, same answer.

Video #276 ยท How it works, by example

Steps in the video

  1. 0:00 200,000 rows: one index turned SCAN into SEARCH
  2. 0:35 The idea
  3. 1:10 Setup
  4. 1:32 A table of made-up orders
  5. 2:00 Why sorted wins
  6. 2:32 Ask SQLite for its plan
  7. 3:01 Run it
  8. 4:10 What this does not show
  9. 4:43 Mistakes to avoid
  10. 5:28 My take and next steps
  11. 6:02 Recap

Files

  • make_db.py (16 lines)
  • plan.py (19 lines)
  • run.py (21 lines)
  • steps.py (14 lines)

Download the code (zip)

Tested with

Python 3.12.15

What this does not show

Made-up orders in an in-memory SQLite database, so no disk reads; the speed-up is shown only as a floor, and the space and write cost of the index was not measured.

Sources

Learning IT by Example.