sql / intermediate
Snippet
Improving Multi-Column Search Performance with Composite Indexes
Composite (multi-column) indexes organize table data using two or more columns in a left-to-right hierarchy. Placing high-cardinality equality columns first dramatically speeds up filtering operations for queries filtering on both attributes.
snippet.sql
sql
1
2
3
4
5
6
CREATE INDEX idx_orders_customer_dateON orders (customer_id, order_date);SELECT order_id, total_amountFROM ordersWHERE customer_id = 1042 AND order_date >= '2026-01-01';
Breakdown
1
CREATE INDEX idx_orders_customer_date
Defines a new index structure named idx_orders_customer_date.
2
ON orders (customer_id, order_date);
Binds the index to the orders table spanning customer_id and order_date.
3
SELECT order_id, total_amount
Specifies the result set attributes to return.
4
FROM orders
Designates the orders table as the query data source.
5
WHERE customer_id = 1042 AND order_date >= '2026-01-01';
Leverages the composite index to satisfy both exact matching and range filtering efficiently.