Course contentsShow
← Data Science
Lesson 1081 of 2,14527. Database Design and OptimizationFree lesson

Composite Indexes and Column Order

Understand multi-column indexes and how column order affects query performance and index usage.

Composite Indexes and Column Order

What You'll Learn

You'll discover how multi-column indexes work and why the order of columns in those indexes dramatically affects which queries can use them efficiently.

The Core Idea

A composite index (or multi-column index) covers multiple columns together, like creating an index on (customer_id, order_date) rather than separate indexes on each column. Think of it like a phone book sorted first by last name, then by first name—you can quickly find "Smith, John" but not "all Johns" efficiently.

The critical insight: column order matters immensely. An index on (A, B, C) works great for queries filtering on:

  • Just A
  • A and B
  • A, B, and C

But it's nearly useless for queries filtering only on B or C without A.

Why Order Matters

Indexes are stored in sorted order. With (customer_id, order_date), rows are sorted by customer_id first, then by order_date within each customer. The database can efficiently scan to a specific customer and then find dates within that customer's records. But if you query for "all orders on a specific date" without filtering by customer, the database must scan the entire index because matching dates are scattered throughout.

Choosing Column Order

Place the most selective (highest filtering power) columns first, and consider your most frequent query patterns:

  • Columns used in equality conditions before range conditions
  • Columns appearing in more queries first
  • Most selective columns (fewest duplicates) typically first

Key Takeaway: Composite indexes are powerful but order-dependent; design them with your WHERE clause patterns in mind, remembering that the leftmost columns must appear in queries for the index to be useful.