learning-it by example

#276 How it works, by example 6.5 min

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.

1. What does SQLite do for a query that filters on email when the column has no index?
2. In a Python SQLite demo with 200,000 made-up orders, what did EXPLAIN QUERY PLAN show for a query by email, before and after creating an index on email?
3. In a Python SQLite demo with 200,000 made-up orders, how many steps did a binary search need to find one customer's email among the sorted rows, compared with a scan?
4. A SQLite table has an index on email, but a query filters on lower(email). What does EXPLAIN QUERY PLAN show, and why?
5. In SQLite, how should you use EXPLAIN QUERY PLAN when working on a slow query?

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

  1. Read the plan

    Copy the code folder first. Run python run.py and find the two plan lines, before and after the index is created. In your own words, say what the words SCAN and SEARCH mean, then check against the video: scan reads every row, search visits only a subset.

    A hint

    The timing line prints only True or False, because exact times change on every run.

  2. Look up a different customer

    In run.py, change the number in EMAIL from 4242 to 7. Predict whether the plan lines change, then run python run.py. When I ran it, the customer had 6 orders, the binary search needed 18 steps, and the plans were the same as before. Change the number back to 4242 afterwards, because the next exercise quotes the answer (8, 26186).

    A hint

    The plan depends on the query and the index, not on which customer you ask for.

  3. Make the index cover the amount

    In run.py, change the index to cover two columns: CREATE INDEX idx_email ON orders (email, amount). Predict how the plan after the index changes, then run python run.py. When I ran it, the plan read SEARCH orders USING COVERING INDEX idx_email (email=?), the answer stayed (8, 26186), and the lower(email) line still said SCAN orders.

    A hint

    The query only needs email and amount, and both are in the index.

  4. Index the lower expression

    In run.py, just before the last print line, add the line db.execute("CREATE INDEX idx_lower ON orders (lower(email))"). Predict the plan for the lower(email) query, then run python run.py. When I ran it, the last line said SEARCH orders USING INDEX idx_lower instead of SCAN orders.

    A hint

    If you need lower, index that exact expression.

These tasks are for you to try on your own machine. Nothing is checked or saved.

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.

Up next in How it works, by example

More in How it works, by example

All How it works, by example videos

Related videos