Database: Choosing Indexes for a Slow Query

A SQL query is slow. Walk through how you diagnose it and what indexes you would create — and what they cost.

Technical Reference & Key Concepts

The Challenge: Fix the Slow Query

You have an orders table with 50M rows. This query is slow:
SELECT * FROM orders WHERE customer_id = ? AND status = 'pending' ORDER BY created_at DESC LIMIT 20;

Core questions to address:

  1. How would you confirm the query is slow and find the current plan?
  2. What index or indexes would you create, and why that shape?
  3. What are the tradeoffs of adding indexes (write cost, storage, duplicate indexes)?