The index exists and the query still scans
There is an index on orders(customer_id, status). The query filters on both columns. EXPLAIN still reports a full table scan.
Explain why, and rewrite the query so the index is used. The schema may not change.
Example
- input
WHERE UPPER(status) = 'SHIPPED' AND customer_id = 42outputtype: ALL, rows: 11284922The index is there. Something in the predicate stops the optimiser from reaching it.
Constraints
- No schema changes — no new index, no generated column.
- The result set must be identical.
Hints
Hint 1
An index stores the column's value, not the value of a function applied to it.
Hint 2
Any function on the indexed side makes the predicate non-sargable.
Hint 3
The same trap catches DATE(created_at) = ... and CAST(id AS CHAR) = ...
Stuck? The lesson behind this problem: 🐘 Query optimisation
sql
Tab indents · Escape first to tab out
Execution service not connectedNothing here can compile or check your answer right now. The test cases below are the specification — work against them in your own editor.
Test cases
These are the specification, not a scoreboard — nothing here runs them.
| Case | Input | Expected |
|---|---|---|
| the index is used | EXPLAIN the rewritten query | type: ref, key: idx_cust_status |
| the rows are unchanged | compare result sets | identical |