learning-it by example

#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.

1. In a SQLite EXPLAIN QUERY PLAN, what is the difference between SCAN and SEARCH, as the SQLite docs describe it in the video?
2. A table has an index on created, but a query that filters on date(created) still shows SCAN orders in its plan. Why?
3. For a query with status = 'refunded' AND created >= '2026-01-01', which index order did the video show to be faster, and what did the SQLite docs say about it?
4. Why does the run print 'at least 20x faster: True' instead of before and after timings in seconds?
5. For ten newest orders, ORDER BY created DESC, id DESC LIMIT 10, the plan after adding an index on created still says SCAN orders. Why was it at least 50 times faster?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

Up next in SQL and analytics

More in SQL and analytics

All SQL and analytics videos

Related videos