Indexes, Part 2: Composite index

Composite Indexes and the Leftmost-Prefix Rule

Say you create an index on (last_name, first_name). That's not two indexes glued together — it's one sorted structure, sorted by last_name first, and within each last_name, sorted by first_name. Like a phone book: sorted by last name, then first name inside that.

Now here's the trap:

• WHERE last_name = 'Smith' → uses the index. Fine, that's the leftmost column.
• WHERE last_name = 'Smith' AND first_name = 'John' → uses the index. Also fine.
• WHERE first_name = 'John' → does NOT use the index.