MongoDB Indexes: Single, Compound, and Multikey

Key Insights

• Single-field indexes optimize queries on one field, while compound indexes support queries on multiple fields with left-to-right prefix matching—order matters significantly for query performance. • Multikey indexes automatically handle array fields by creating index entries for each array element, enabling efficient queries on array contents without special syntax. • Index intersection allows MongoDB to combine multiple indexes for complex queries, but compound indexes generally outperform intersected single-field indexes for predictable query patterns.

Understanding MongoDB Index Fundamentals

MongoDB uses B-tree indexes to reduce query execution time from collection scans to logarithmic lookups. Without indexes, MongoDB performs collection scans that examine every document. For a collection with millions of documents, this becomes prohibitively expensive.

Indexes store a sorted representation of field values with pointers to documents. This structure enables binary search operations, reducing query complexity from O(n) to O(log n).

Check existing indexes on a collection:

db.users.getIndexes()

Every collection automatically includes an index on _id. Additional indexes require explicit creation based on query patterns.

Single-Field Indexes

Single-field indexes optimize queries that filter, sort, or project on one field. Create them for frequently queried fields:

db.users.createIndex({ email: 1 })

The value 1 specifies ascending order, -1 specifies descending. For single-field indexes, direction rarely matters since MongoDB can traverse indexes bidirectionally.

This index accelerates queries like:

db.users.find({ email: "user@example.com" })
db.users.find({ email: /^admin/ })
db.users.find({ email: { $exists: true } }).sort({ email: 1 })

Verify index usage with explain():

db.users.find({ email: "user@example.com" }).explain("executionStats")

Look for "stage": "IXSCAN" in the winning plan, indicating index usage. The executionStats shows totalDocsExamined versus nReturned—ideally these numbers match closely for efficient queries.

Compound Indexes

Compound indexes include multiple fields and support queries on any prefix of the indexed fields. The field order critically impacts which queries the index can optimize.

db.orders.createIndex({ customerId: 1, orderDate: -1, status: 1 })

This index efficiently supports queries on:

customerId alone
customerId and orderDate
customerId, orderDate, and status

It cannot efficiently support queries on orderDate alone or status alone because these fields don’t form a left-to-right prefix.

// Uses index efficiently
db.orders.find({ customerId: 123 })
db.orders.find({ customerId: 123, orderDate: { $gte: ISODate("2024-01-01") } })

// Cannot use index efficiently (no customerId prefix)
db.orders.find({ orderDate: { $gte: ISODate("2024-01-01") } })
db.orders.find({ status: "pending" })

ESR Rule for Compound Index Design

Design compound indexes using the ESR (Equality, Sort, Range) rule:

Equality fields first (exact matches)
Sort fields second
Range fields last (inequalities, ranges)

// Query pattern
db.products.find({ 
  category: "electronics",      // Equality
  price: { $gte: 100, $lte: 500 }  // Range
}).sort({ rating: -1 })           // Sort

// Optimal index following ESR
db.products.createIndex({ 
  category: 1,   // Equality
  rating: -1,    // Sort
  price: 1       // Range
})

Placing range fields before sort fields forces MongoDB to sort in memory rather than using the index order.

Multikey Indexes

Multikey indexes handle array fields by creating index entries for each array element. MongoDB automatically creates multikey indexes when you index a field containing arrays.

db.articles.insertMany([
  { title: "MongoDB Basics", tags: ["database", "nosql", "mongodb"] },
  { title: "Index Strategies", tags: ["database", "performance", "indexes"] }
])

db.articles.createIndex({ tags: 1 })

This single index creation produces multiple index entries per document—one for each array element:

// Efficiently finds all articles tagged "database"
db.articles.find({ tags: "database" })

// Also works with array operators
db.articles.find({ tags: { $in: ["mongodb", "performance"] } })

Multikey Index Limitations

You cannot create compound indexes with multiple array fields:

// This fails if both tags and categories contain arrays
db.articles.createIndex({ tags: 1, categories: 1 })
// Error: cannot index parallel arrays

However, compound multikey indexes work when only one field contains arrays:

db.articles.createIndex({ author: 1, tags: 1 })

// Efficiently queries on author and any tag
db.articles.find({ author: "John Doe", tags: "mongodb" })

Multikey Index Bounds

Multikey indexes can use index bounds for array queries:

db.inventory.createIndex({ "specs.values": 1 })

// Uses index bounds
db.inventory.find({ "specs.values": { $gt: 50, $lt: 100 } })

// Less efficient - examines more index entries
db.inventory.find({ "specs.values": { $elemMatch: { $gt: 50, $lt: 100 } } })

Index Intersection

MongoDB can combine multiple indexes for queries involving multiple fields. However, this is less efficient than purpose-built compound indexes.

db.products.createIndex({ category: 1 })
db.products.createIndex({ price: 1 })

// MongoDB may intersect both indexes
db.products.find({ category: "electronics", price: { $lt: 500 } })

Check the query plan:

db.products.find({ 
  category: "electronics", 
  price: { $lt: 500 } 
}).explain("executionStats")

If you see "stage": "AND_SORTED" or "AND_HASH", MongoDB is intersecting indexes. For frequently-run queries, create a dedicated compound index:

db.products.createIndex({ category: 1, price: 1 })

Practical Index Strategy

Start with these guidelines:

Analyze query patterns first:

// Enable profiling to capture slow queries
db.setProfilingLevel(1, { slowms: 100 })

// Review slow queries
db.system.profile.find({ millis: { $gt: 100 } }).sort({ ts: -1 }).limit(10)

Create indexes for your most common queries:

// For user authentication
db.users.createIndex({ email: 1 }, { unique: true })

// For time-series queries
db.events.createIndex({ userId: 1, timestamp: -1 })

// For full-text search
db.articles.createIndex({ title: "text", content: "text" })

Monitor index usage:

db.orders.aggregate([
  { $indexStats: {} }
])

Remove unused indexes—they consume memory and slow down writes:

db.orders.dropIndex("oldIndex_1")

Consider covered queries where indexes contain all queried fields:

db.users.createIndex({ email: 1, name: 1, status: 1 })

// Covered query - never touches documents
db.users.find(
  { email: "user@example.com" },
  { _id: 0, name: 1, status: 1 }
)

Indexes transform MongoDB performance from unusable to production-ready. Design them based on actual query patterns, understand the prefix rule for compound indexes, and leverage multikey indexes for array fields. Monitor performance continuously and adjust as usage patterns evolve.