Query-tuning exercises
Twelve slow queries from a real-looking shop. Fix each one; the database checks your plan.
Draft. This page is new and has not yet been reviewed by a second person. If something looks wrong, report it.
Before thisReading EXPLAIN
On this page
Twelve slow queries from an online shop, each with a real cause and a real fix. For each one, read the scenario, explain the query, decide what is wrong, and change the schema or the query. The database judges your answer by the plan it actually chooses, not by matching your text, so any correct fix passes.
How each check works:
- Your changes (for example a
CREATE INDEX) and your query run inside a transaction that is rolled back afterwards, so every attempt starts from the same data. - Checks look at the plan: which kinds of step it uses, how many pages it reads, how many rows it reads and throws away. Never at timings, which depend on your machine.
- Your query must return exactly the same rows as the original. Making a query fast by making it wrong does not count.
- Hints go from a nudge to the answer. Your attempts and solved exercises are saved in this browser and shown on your progress page.
If you are new to plans, read Reading EXPLAIN first, or warm up in the playground.