sql · level 3 · unit 6 · lesson 4
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
An lets the database jump straight to the rows it needs.
To find the parcels for one postcode, the database reads every row. Ten rows is nothing, ten million is slow. An index is like the index at the back of a book: a sorted list of values, each pointing to its rows.
CREATE INDEX idx_parcels_postcode ON parcels (postcode); builds one. To check it helps, put before a query: you get the plan instead of the rows.
means every row gets read. means the database jumps straight to the matches.
EXPLAIN QUERY PLAN
SELECT * FROM parcels WHERE postcode = 'LS1 4AB';
CREATE INDEX idx_parcels_postcode ON parcels (postcode);
EXPLAIN QUERY PLAN
SELECT * FROM parcels WHERE postcode = 'LS1 4AB';The numbers in a plan change between SQLite versions, so read the words in the detail column.
guess first
Count, drop the index, count again. What shows?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
CREATE INDEX idx_parcels_depot ON parcels (depot);
SELECT COUNT(*) AS north FROM parcels WHERE depot = 'North';
DROP INDEX idx_parcels_depot;
SELECT COUNT(*) AS north FROM parcels WHERE depot = 'North';your turn
Your turn. Speed up the daily round.
The drivers look up parcels by day all the time. Fill the blank so the index is built on the right column, then check the plan says SEARCH.
result
Your answer appears here as a table.
fix it
There is an index, but the plan still scans. Fix it!
result
Your answer appears here as a table.
- The plan says
SCAN. Which column does the query'sWHERElook at? - An index only helps a query that searches the column it was built on.
- Build the index on depot instead, named idx_parcels_depot, then press run.
nailed it
your queries know the shortcut now. :D
- An index lets the database jump straight to matching rows.
EXPLAIN QUERY PLANshowsSCANorSEARCHinstead of the rows.- An index changes the speed, never the answer.
new words in your collection
- index
- EXPLAIN QUERY PLAN
- SCAN
- SEARCH
This lesson isn’t saved. A free account remembers every lesson you finish.