Why should you care?

“Orders for customer sorted by date” is one index seek — (customer_id, placed_at) matches filter + ORDER BY.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE INDEX idx_orders_cust_date ON orders(customer_id, placed_at);
EXPLAIN SELECT order_id, total_inr
FROM orders
WHERE customer_id = 42
ORDER BY placed_at DESC
LIMIT 20;

What happened?

• Optimizer uses idx_orders_cust_date for filter on customer_id and avoids filesort for placed_at DESC within that customer.
• Column order in index definition matters.

Practice next

1. CREATE composite index.
2. EXPLAIN query — note key, Extra without Using filesort ideally.
3. Try WHERE placed_at only — index may not be used.
4. Add covering columns in next lesson: (customer_id, placed_at, total_inr).
5. Use INDEX idx_orders_cust_date in hint to force plan (dev only).

Remember

Column order matches filter/selectivity pattern. Leftmost prefix must match WHERE leading columns. Can satisfy ORDER BY within same index.

DataFlow account history

Mobile “my orders” screen uses composite index — no sort temp table.

Outcome: Smooth scroll through years of orders.