learning-it by example

Back to the video

Five Index Rules for Slow SQL, Checked in SQLite

On 200,000 made-up orders, one index turned a full scan into a search that was at least 20 times faster.

Video #135 ยท SQL and analytics

Steps in the video

  1. 0:00 One index: from reading every row to at least 20 times faster
  2. 0:24 The idea
  3. 0:49 Setup
  4. 1:16 Two hundred thousand made-up orders
  5. 1:34 Reading the plan
  6. 1:58 One index at a time
  7. 2:11 Before and after
  8. 2:29 Rules one and two
  9. 3:30 Rules three to five
  10. 5:01 What this does not show
  11. 5:25 Mistakes to avoid
  12. 5:51 My take and next steps
  13. 6:15 Recap

Files

  • orders.py (19 lines)
  • rules.py (23 lines)
  • run.py (42 lines)

Download the code (zip)

Tested with

Python 3.12.15

What this does not show

Made-up, in-memory orders on one machine; speed is shown only as loose at-least checks, with no ANALYZE, no write cost and no other databases.

Sources

Learning IT by Example.