sql / intermediate
Snippet
Optimizing Multi-Column Search Efficiency with Composite Indexes
Composite indexes index multiple columns in a specified order. Under ANSI SQL standard query optimization principles, column ordering should follow high selectivity and the leftmost prefix rule.
snippet.sql
sql
1
2
CREATE INDEX idx_orders_customer_statusON orders (customer_id, order_status, order_date);
Breakdown
1
CREATE INDEX idx_orders_customer_status
Creates a multi-column composite index on the target table.
2
ON orders (customer_id, order_status, order_date);
Orders key columns according to selective equality search criteria (leftmost prefix rule).