Performance

Composite Indexes: When One Column Isn't Enough

SQL Mastery Team
April 19, 2026
5 min read

Welcome to **Day 88**. Sometimes an index on one column isn't enough. If you frequently search by `first_name` AND `last_name`, you need a **Composite Index**.

Creating a Multi-Column Index

CREATE INDEX idx_user_full_name ON users (last_name, first_name);

The "Left-to-Right" Rule (Crucial!)

A composite index on `(last_name, first_name)` is basically sorted like a phone book.

1. It's sorted by **last_name** first.

2. Then, for people with the same last name, it's sorted by **first_name**.

Which queries can use this index?

  • ✅ `WHERE last_name = 'Smith'`
  • ✅ `WHERE last_name = 'Smith' AND first_name = 'John'`
  • ❌ `WHERE first_name = 'John'` (The database can't use the index because the primary sort—last name—is missing!)

    • Senior Strategy

    Always put the most "Selective" column (the one that filters out the most rows) as the **first** column in your composite index.

    Your Task for Today

    Identify a query with two filters in the `WHERE` clause. Practice writing a composite index for it, paying attention to the column order.

    *Day 89: EXPLAIN ANALYZE—Reading the Database's Mind.*

    Ready to put your knowledge into practice?

    Join SQL Mastery and learn through interactive exercises.