CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 3 of 5 · 1 min read

Indexing more than one column

A composite index, and why column order inside it matters.

Think of a phone book sorted by last name, then by first name within each last name. Finding "Rao, Asha" is fast. Finding everyone with the first name "Asha," regardless of last name, is not helped by this same sorted order at all.

In plain words: a composite index covers more than one column, in a specific order. It speeds up a query filtering on the first column alone, or on the first column together with the second, but does not help a query filtering only on the second column without the first.

A worked example. An index on (customer_id, placed_on) speeds up finding one customer's orders, and finding one customer's orders within a date range, but not finding all orders on one specific date across every customer.

The key lines.

CREATE INDEX idx_orders_customer_date ON orders (customer_id, placed_on);

SELECT * FROM orders WHERE customer_id = 107 AND placed_on >= '2026-01-01'; -- uses the index well
SELECT * FROM orders WHERE placed_on >= '2026-01-01'; -- does not use this index well

Composite index

A composite index covers more than one column, in a declared order. It helps queries filtering on a leading prefix of that column order, but not queries skipping straight to a later column alone.

Example: (customer_id, placed_on) helps a query naming customer_id, with or without placed_on, but not a query naming only placed_on.