An index exists on (customer_id, status, placed_at), but a query filtering on customer_id and ordering by placed_at DESC LIMIT 20 is still slow. Why, and how would you fix the index?

A composite index is sorted by its first column, then within each value of that by its second column, and so on — so (customer_id, status, placed_at) orders rows by date only within each (customer_id, status) pair, not overall. A query that filters on customer_id alone and wants the newest 20 rows by placed_at cannot stop early using this index: status sits between the equality column and the date, so the engine has to read every row for that customer across every status value, sort them all by date, and only then take the top 20. The fix is to put the date column right after the true equality columns — (customer_id, placed_at DESC) — so the index itself is already in the order the query needs, checking status as a filter along the way.