Composite index là gì? Column ordering trong index quan trọng như thế nào?

Composite index tuân theo leftmost prefix rule: index (a,b,c) dùng được cho (a), (a,b), (a,b,c) nhưng không cho (b) hay (c) đơn lẻ; đặt equality columns trước, range columns sau.

Composite index trên (a, b, c): có thể dùng cho filter trên (a), (a,b), (a,b,c) — leftmost prefix rule.

KHÔNG dùng được cho (b) alone, (c) alone, hay (b,c) vì b không phải leftmost prefix.

Ví dụ thực tế: index (status, created_at) — query WHERE status = 'active' ORDER BY created_at DESC dùng được hoàn toàn; query WHERE created_at > '2024-01-01' không dùng được vì bỏ qua status.

Index skip scan (PostgreSQL 13+): trong một số cases optimizer có thể skip leading columns nếu cardinality thấp — nhưng không nên rely on.

Thứ tự cột tối ưu:

1. equality conditions trước (=),
2. range conditions sau (>, <, BETWEEN),
3. ORDER BY columns cuối — vì sau range condition, index không hỗ trợ sort nữa

Ví dụ: WHERE user_id = ? AND status = ? AND created_at > ? → index (user_id, status, created_at).

Covering composite index: CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at, amount) cover SELECT created_at, amount FROM orders WHERE user_id = ? AND status = ? — Index Only Scan, không đọc table.

Pitfall: index (a, b) và index (b, a) là hai indexes khác nhau phục vụ different query patterns — đừng nhầm lẫn.