Diagnose a Slow SQL Query
An orders query filters by customer_id, sorts by created_at DESC, and returns 20 rows. Propose an index and describe how to verify it.
Examples
Compare approaches
Baseline approach
Begin by explaining the core mechanism, stating assumptions, and walking through one concrete example.
Time: Depends on design · Space: Depends on design
SELECT id, created_at FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
Strong answer
Consider an index on (customer_id, created_at DESC). Compare EXPLAIN ANALYZE with realistic data and check write overhead, selectivity, and row visibility.
Time: Discuss operation costs · Space: Discuss retained state
SELECT id, created_at FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
Common traps
- An index is not automatically useful for every data distribution.
- State assumptions and justify trade-offs rather than memorizing a single answer.