Compound Index vs Single Index in MongoDB: Key Differences and Usage

Use a single index in MongoDB when you need to optimize queries filtering or sorting on a single field. Use a compound index when queries filter or sort on multiple fields together, improving performance by covering those combined fields in one index.

Quick Comparison

Here is a quick comparison between single index and compound index in MongoDB based on key factors.

Factor	Single Index	Compound Index
Definition	Index on one field	Index on multiple fields in a defined order
Use Case	Queries on one field	Queries on multiple fields combined
Storage Size	Smaller, simpler	Larger, more complex
Query Performance	Optimizes single-field queries	Optimizes multi-field queries and sort
Index Prefix	N/A	Can support queries on leading fields only
Maintenance Overhead	Lower	Higher due to complexity

Key Differences

A single index in MongoDB is created on one field and is best when your queries filter or sort by that single field. It is simple, uses less storage, and is easier to maintain. However, it cannot efficiently support queries that involve multiple fields together.

A compound index is created on multiple fields in a specific order. It helps MongoDB quickly find documents when queries filter or sort by those fields combined. Compound indexes can also support queries on the leading fields of the index, but not on fields later in the index without the earlier ones. They require more storage and maintenance but improve performance for complex queries.

Choosing between them depends on your query patterns: if you mostly query by one field, use a single index; if you query by multiple fields together, a compound index is better.

Code Comparison

Creating a single index on the age field in a MongoDB collection:

mongodb

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

// Query using the single index
db.users.find({ age: { $gt: 25 } })

Output

Creates an ascending index on 'age'. The query uses this index to find users older than 25.

Compound Index Equivalent

Creating a compound index on age and city fields to optimize queries filtering by both:

mongodb

db.users.createIndex({ age: 1, city: 1 })

// Query using the compound index
db.users.find({ age: { $gt: 25 }, city: 'New York' })

Output

Creates an ascending compound index on 'age' and 'city'. The query uses this index to efficiently find users older than 25 living in New York.

When to Use Which

Choose a single index when your queries mostly filter or sort on one field alone, keeping indexes simple and storage low.

Choose a compound index when your queries filter or sort on multiple fields together, especially if those fields are often used in combination. This improves query speed but uses more storage and requires careful index order planning.

Remember, compound indexes can also serve queries on the leading fields, so design them based on your most common query patterns.

Key Takeaways

Use single indexes for queries on one field to keep indexes simple and efficient.

Use compound indexes for queries filtering or sorting on multiple fields together.

Compound indexes support queries on their leading fields but require careful order planning.

Compound indexes use more storage and have higher maintenance than single indexes.

Choose index type based on your application's most frequent query patterns.