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
- 0:00 One index: from reading every row to at least 20 times faster
- 0:24 The idea
- 0:49 Setup
- 1:16 Two hundred thousand made-up orders
- 1:34 Reading the plan
- 1:58 One index at a time
- 2:11 Before and after
- 2:29 Rules one and two
- 3:30 Rules three to five
- 5:01 What this does not show
- 5:25 Mistakes to avoid
- 5:51 My take and next steps
- 6:15 Recap
Files
- orders.py (19 lines)
- rules.py (23 lines)
- run.py (42 lines)
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.