sql · level 3 · unit 7 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
A helps queries that start with its first column.
CREATE INDEX idx_depot_day ON parcels (depot, day); builds a composite index. It is sorted by depot, then by day within each depot, like a phone book sorted by surname, then first name.
It helps a search on depot, or on depot and day together. A search on day alone cannot use it, in the same way a phone book cannot find everyone called Sam. This is the leftmost column rule.
When the index holds every column a query needs, it is a , and the plan says USING COVERING INDEX.
CREATE INDEX idx_depot_day ON parcels (depot, day);
EXPLAIN QUERY PLAN
SELECT * FROM parcels WHERE depot = 'North' AND day >= '2024-07-03';
EXPLAIN QUERY PLAN
SELECT * FROM parcels WHERE day = '2024-07-03';Wrapping a column in a function, as in lower(depot), also stops its index helping.
guess first
Exact text or lowered text. Which count finds the parcels?
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 exact FROM parcels WHERE depot = 'north';
SELECT COUNT(*) AS lowered FROM parcels WHERE lower(depot) = 'north';your turn
Your turn. Make the index cover the query.
The query needs depot and day. Fill the blank so the index holds both, then check the plan says USING COVERING INDEX.
result
Your answer appears here as a table.
fix it
The depot search will not use the index. Fix it!
result
Your answer appears here as a table.
- The plan says
SCAN. Which column does the index start with, and which one does the query search? - A composite index only helps when the query uses its first column, and this one starts with day.
- Build it as idx_depot_day ON parcels (depot, day), then press run.
nailed it
your indexes fit your queries. :D
- A composite index helps searches that start with its first column.
- Wrapping a column in a function stops its index helping.
- A covering index answers a query without reading the table.
new words in your collection
- composite index
- covering index
This lesson isn’t saved. A free account remembers every lesson you finish.