Skip to content

Composite Index

In one line

Composite indexes cover multiple columns — speed up queries filtering on several columns at once.

In simple words

A composite (multi-column) index indexes multiple columns together. The column order matters — it determines which queries can use the index.

CREATE INDEX idx ON orders(customer_id, order_date)

This index helps:

  • WHERE customer_id = 5 — uses first column
  • WHERE customer_id = 5 AND order_date > '2024-01-01' — uses both columns
  • WHERE customer_id = 5 ORDER BY order_date — uses both (sorting)

But NOT:

  • WHERE order_date > '2024-01-01' — can't use index (order_date is second)
  • WHERE order_date > '2024-01-01' AND customer_id = 5 — partially (only customer_id part)

Rule: put the most selective column first, and columns used in equality before range. The leftmost prefix rule determines index usage.

Example

sql
-- Composite index on (customer_id, order_date, status)
CREATE INDEX idx_orders_comp ON orders(customer_id, order_date, status);

-- Uses all 3 columns (optimal):
SELECT * FROM orders
WHERE customer_id = 42 AND order_date = '2024-01-15' AND status = 'shipped';

-- Uses first 2 columns:
SELECT * FROM orders WHERE customer_id = 42 AND order_date = '2024-01-15';

-- Uses first 1 column only:
SELECT * FROM orders WHERE customer_id = 42;

-- CANNOT use index (skips customer_id):
SELECT * FROM orders WHERE order_date = '2024-01-15';
-- Must scan the whole table or use another index

Composite index follows the leftmost prefix rule: it can use columns from left to right, but can't skip the first column. Order matters!

Common confusions

  • Confused with: single-column index

    The difference: Single-column indexes help queries on one column. Composite indexes help queries on multiple columns simultaneously. A composite index on (A, B) is not the same as two single indexes on A and B — the composite can filter on both at once, but the singles can't be combined efficiently.

Related terms

Related Resources

Related Lessons

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to glossary