Playground Checkpoint
Design and build a real schema — no auto-grading, just a real attempt.
The Challenge
Mike needs a real report this time: every cancelled order from all of 2020, newest first, for an end-of-year review. Sarah writes the query, hits run, and watches it crawl.
Open the Playground and fix it yourself: find out exactly why it's slow, then design a single index that actually solves it — not just the filtering, but the sorting too.
What Your Schema Needs
- Run EXPLAIN QUERY PLAN on the report query exactly as given, and read both lines of the plan it produces.
- Design and create ONE index that fixes the report — think about which column is being filtered by equality, which by a range, and which order those belong in in a composite index.
- Re-run EXPLAIN QUERY PLAN on the exact same report query and confirm it now reads as a SEARCH using your index, with no separate sorting step left in the plan.
- Confirm the report still returns exactly 19 rows before and after — the fix must never change what the report returns, only how much work it takes to produce it.
Stuck? A Few Hints
- A composite index's column order matters: put the column used for an exact match (Status = 'Cancelled') before the column used for a range (OrderDate >= ... AND OrderDate < ...).
- If a composite index's trailing column already matches what the query's ORDER BY needs — for the one Status value being filtered — SQLite can walk the index in that order directly, with no separate temporary sort.
- One index, built on the right two columns in the right order, can eliminate both the SCAN and the USE TEMP B-TREE FOR ORDER BY step at the same time.
Ready to Build It?
Opens the Playground, right in your browser — nothing to install.
Before You Move On
Every idea from this Act meets here — a full scan diagnosed correctly, a B-Tree index built with the right column order for real selectivity, and a plan read closely enough to notice it solved two problems, not just one. GreenMart's reports run the way they used to feel: instant. The next Act moves past any single query entirely — running a real, multi-city database is about more than making it fast.