sql / intermediate
Snippet
Creating Composite Indexes for Multi-Column Filters
A composite index covers multiple columns in a table. By arranging columns according to query filter patterns and sort direction, database engines can satisfy filtered range queries quickly using index scans.
snippet.sql
sql
1
2
3
4
5
6
CREATE INDEX idx_orders_customer_dateON orders (customer_id, order_date DESC);SELECT order_id, total_amountFROM ordersWHERE customer_id = 4509 AND order_date >= '2026-01-01';
Breakdown
1
CREATE INDEX idx_orders_customer_date
Initiates the creation of a named multi-column composite index.
2
ON orders (customer_id, order_date DESC);
Specifies the target table and columns, ordering dates descending for efficient range lookup.
3
SELECT order_id, total_amount
Retrieves relevant columns from the order records.
4
FROM orders
Identifies the target table for the query execution.
5
WHERE customer_id = 4509 AND order_date >= '2026-01-01';
Leverages the composite index left-to-right to quickly narrow down matching rows.