Question

Design the index for a hot query on a 2B-row orders table that's slow despite 'having an index'. The query is: WHERE merchant_id = ? AND status = 'open' ORDER BY created_at DESC LIMIT 50, run ~30k times/sec for merchant dashboards. There's a single-column index on merchant_id but the database still sorts thousands of rows and does thousands of row fetches per query. Design the right index (column order, what it covers), explain why the current one is slow, and the write-side cost you're accepting.