Composite Indexes

A composite index sorts by its first column, then the second, like a phone book, and seeks on a leftmost prefix only: (customer_id, status, ordered_at) serves customer_id, then status, then ordered_at, never status alone. Put equality columns first and one range or sort column last; a range or a gap stops the narrowing.

ALTER TABLE big_orders DROP INDEX idx_customer,
  ADD INDEX idx_cust_status_date (customer_id, status, ordered_at);
EXPLAIN SELECT * FROM big_orders WHERE customer_id = 4242 AND ordered_at >= '2026-01-01'\G
CREATE INDEX idx_status_date ON big_orders (status, ordered_at);
SELECT COUNT(*) INTO @n FROM big_orders WHERE ordered_at >= '2026-08-29';
SELECT /*+ NO_SKIP_SCAN(big_orders) */ COUNT(*) INTO @n FROM big_orders
WHERE ordered_at >= '2026-08-29';

Query OK, 0 rows affected (2.380 sec)
Records: 0  Duplicates: 0  Warnings: 0
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on big_orders using idx_cust_status_date (customer_id = 4242), with
  index condition: (big_orders.ordered_at >= TIMESTAMP'2026-01-01 00:00:00')  (cost=1.25
    rows=15)
1 row in set (0.001 sec)
Query OK, 0 rows affected (2.068 sec)
Records: 0  Duplicates: 0  Warnings: 0
Query OK, 1 row affected (0.001 sec)
Query OK, 1 row affected (0.221 sec)

The plan seeks on customer_id alone, because status is missing, and checks the date inside the index (index condition pushdown). Skip scan (MySQL 8.0.13 524 ) answered the date query in a millisecond, against 0.22 s without it, by seeking each status in turn. It needs one table, no GROUP BY or DISTINCT, only indexed columns, constant equalities before the skipped columns, and a range after them.