#135 SQL and analytics 6.7 min
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.
Try another customer
Copy the code folder first. In rules.py change customer_id = 4242 to customer_id = 17 in rule 1, then run python run.py 1. Predict whether the plans and the 'at least 20x faster' check change. When I ran it, the plans were the same (SCAN orders, then SEARCH using by_customer) and both checks still printed True.
A hint
The index is on the column, not on one customer, so any customer id can use it.
Break rule two on purpose
In rules.py, in rule 2, change the 'after' query from created = '2026-03-05' to date(created) = '2026-03-05' so both sides use the function. Run python run.py 2. Predict what the 'now' plan will say and whether 'at least 10x faster' stays True. When I ran it, both plans said SCAN orders, the answers were the same, and the check printed False.
A hint
Keep the index on created in place. It does not help when the column is wrapped in a function.
Swap the column order in rule three
In rules.py, in rule 3, change the 'after' index from (status, created) to (created, status), so it matches the 'before' index, then run python run.py 3. Predict what the 'now' plan will use. When I ran it, the plan used only created>? with the index, and 'at least 5x faster' printed False.
A hint
Compare the part in brackets at the end of the plan line with the original run.
Read the plan before the timing
Run python run.py 4 and python run.py 5 and write down, for each rule, what changed in the plan between 'was' and 'now' in your own words. Then predict whether the first word of the plan, SCAN or SEARCH, changes in either rule. When I ran it, it changed in neither: rule 4 stays SEARCH and gains the words COVERING INDEX, and rule 5 stays SCAN, gains USING INDEX and loses USE TEMP B-TREE FOR ORDER BY. So read the whole plan line, not only its first word.
A hint
Look at the words COVERING INDEX and USE TEMP B-TREE.
These tasks are for you to try on your own machine. Nothing is checked or saved.
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.
