Course contentsShow
← System Design
Lesson 280 of 1,9198. Database Performance OptimizationFree lesson

Index Merge and Multi-Column Indexes

When databases combine indexes versus using composite indexes for queries with multiple WHERE clauses.

Index Merge and Multi-Column Indexes

You'll learn: How databases handle queries with multiple WHERE conditions and when to use composite indexes instead of letting the database merge separate indexes.

The Two Approaches

When your query filters on multiple columns like WHERE user_id = 123 AND status = 'active', the database has two strategies:

Index Merge: Use separate indexes on user_id and status, fetch matching rows from each, then intersect the results. Think of this like two librarians searching different card catalogs, then comparing notes to find books that match both criteria.

Multi-Column (Composite) Index: A single index on (user_id, status) together. This is like a phone book sorted by last name then first name—both criteria are baked into one ordered structure.

When Index Merge Happens

Databases can combine indexes automatically, but it's often inefficient:

  • Two separate index lookups
  • Merging result sets in memory
  • Extra overhead vs. a single lookup

Why Composite Indexes Win

A multi-column index on (user_id, status) allows the database to jump directly to matching rows in one operation. The columns are ordered (left-to-right matters!), so queries filtering on user_id alone still benefit, but queries filtering only status won't use this index.

Planning Your Indexes

  • Create composite indexes for common multi-condition queries
  • Don't rely on index merges for performance-critical paths
  • Test with explain plans (covered in lesson 277) to verify your strategy

Key Takeaway: While databases can merge separate indexes, explicitly creating multi-column indexes for your common query patterns delivers better performance by eliminating merge overhead and reducing lookups.