How do composite and covering indexes work, and how do you choose the column order?
How do composite and covering indexes work, and how do you choose the column order?
A composite (multi-column) index is sorted by its first column, then by the second within equal first values, and so on. A covering index contains every column a query needs, so the table is never touched.
CREATE INDEX idx_orders_cust_status_date ON orders (customer_id, status, created_at);
The index above can serve:
WHERE customer_id = ?WHERE customer_id = ? AND status = ?WHERE customer_id = ? AND status = ? AND created_at > ?It cannot efficiently serve WHERE status = ? or WHERE created_at > ? alone, because the data is not sorted by those columns across the whole index.
=, IN).>, <, BETWEEN); columns after a range column cannot narrow the scan.ORDER BY, in the same direction, so the index delivers rows pre-sorted and skips the sort.Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.