Loadingone moment…
Sign in to run and submit your work
Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.
or
CODE WORKSPACE
The query log records how many rows each statement shape had to examine and how many it returned. A query that reads two million rows to return three hundred is not using an index, whatever the index list says.
Return the query shapes that read more than ten rows for every row returned, with that ratio. Order by the ratio descending, then by query_id.
Result columns · in this order
query_id | Statement identifier. |
statement | What the query is for. |
rows_examined | Rows the engine read. |
rows_returned | Rows the query returned. |
rows_read_per_row_returned | Rows examined per row returned, to one decimal place. Higher is worse. |
How to approach it
Divide examined by returned in decimal arithmetic, filter above ten, and sort worst first.
Sample input
| query_id | statement | predicate | rows_examined | rows_returned | seconds |
|---|---|---|---|---|---|
| 1 | order lookup by id | order_id = 84213 | 1 | 1 | 0.002 |
| 2 | orders in March | created_at >= '2026-03-01' AND created_at < '2026-04-01' | 166000 | 166000 | 0.34 |
| 3 | orders in March, wrapped | strftime('%Y-%m', created_at) = '2026-03' | 2000000 | 166000 | 4.1 |
| 4 | customer email search | LOWER(email) LIKE '%@acme.com' | 410000 | 38 | 1.9 |
| 5 | customer email exact | email = 'ada@acme.com' | 1 | 1 | 0.001 |
| 6 | orders by status | status = 'pending' | 2000000 | 4200 | 2.6 |
| 7 | coupon lookup | coupon_code = 'SPRING26' | 2000000 | 310 | 2.55 |
| 8 | items for an order | order_id = 84213 | 5 | 5 | 0.003 |
| 9 | sku prefix | sku LIKE 'SKU-A%' | 9400000 | 1100 | 6.4 |
| 10 | sku contains | sku LIKE '%-A-%' | 9400000 | 900 | 6.9 |
10 rows — all rows shown.
Expected output
| query_id | statement | rows_examined | rows_returned | rows_read_per_row_returned |
|---|---|---|---|---|
| 4 | customer email search | 410000 | 38 | 10789.5 |
| 10 | sku contains | 9400000 | 900 | 10444.4 |
| 9 | sku prefix | 9400000 | 1100 | 8545.5 |
| 7 | coupon lookup | 2000000 | 310 | 6451.6 |
| 6 | orders by status | 2000000 | 4200 | 476.2 |
| 3 | orders in March, wrapped | 2000000 | 166000 | 12 |
6 rows — all rows shown.
Constraints
rows_examined / rows_returned, in decimal arithmetic, rounded to one decimal place.rows_returned = 0, so no divide-by-zero guard is needed — but say what you would add if one appeared.query_id.Worked example
Compare query 2 with query 3. Both filter orders on created_at for March. Query 2 writes a half-open range and examines 166,000 rows. Query 3 writes strftime('%Y-%m', created_at) = '2026-03' and examines 2,000,000 — the function around the column means the index on it cannot be used at all.
Queries 4 and 5 are the same pair on email: an equality examines 1 row, a leading-wildcard LIKE examines all 410,000. A B-tree can seek on a known prefix and has nothing to seek on when the pattern starts with %.
Queries 6 and 7 are different: the predicates are sargable, and the columns are simply not selective enough for an index to be worth using. Same symptom, different cause, different fix — which is why the ratio alone does not tell you what to do.
What this tests
Whether you can spot a non-sargable predicate from its cost profile, and whether you know that a bad ratio has two quite different causes.
Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.
This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.