Key Takeaways
- Compound indexes allow more efficient querying by indexing multiple fields in a single index.
- The order of fields in a compound index affects its efficiency, following the Equality, Sort, Range (ESR) rule optimizes performance.
- Indexes improve read performance but need careful management to avoid impacting write operations.
Preface: What is an Index?
In MongoDB, an index lets you query data more efficiently. It stores a subset of your collection in a B-Tree, speeding up queries by avoiding full collection scans.
While improving read operations, indexes require maintenance on write operations as they must be updated whenever documents change.
Create an index in MongoDB like this:
db.people.createIndex({name: 1})
This creates a single field index on the name field of the people collection, enhancing queries like:
db.people.find({name: "Eric"})
What is a Compound Index?
A compound index is a single index that includes multiple fields, allowing for complex queries to be processed more efficiently.
Creating a Compound Index in MongoDB
db.people.createIndex({name: 1, email: -1})
This code creates an index on the people collection, sorting by name in ascending order, then by email in descending order. This order is crucial for efficiently handling certain queries.
How Does a Compound Index Work?
Compound indexes store data in a multi-level sorted B-Tree. While they duplicate data, they accelerate lookups significantly by enabling quick traversal.
For example, the compound index created by:
db.people.createIndex({name: 1, email: -1})
sorts the people collection by name and within each name sorts email in descending order. This makes queries like:
db.people.find({name: "Sam", email: "fred@gmail.com"})
much faster by leveraging this ordered structure.
Order Matters!
The order of fields in a compound index is crucial. For instance, creating:
db.people.createIndex({name: 1, email: -1})
won't speed up:
db.people.find({email: "sam@gmail.com"})
because the index primarily sorts by name first. MongoDB uses prefixes to evaluate compound indexes.
Understanding Prefixes
A prefix in a compound index refers to the initial segment of fields in index order. Consider:
db.people.createIndex({name: 1, email: 1, address: 1})
It supports queries on the following prefixes:
- {name: 1}
- {name: 1, email: 1}
- {name: 1, email: 1, address: 1}
This means it doesn't support queries solely on email or address fields without the preceding prefixes.
When to Use a Compound Index
Compound indexes are ideal for collections queried frequently using multiple fields. They're particularly useful when queries involve equality, sorting, and ranges. A compound index can be created following the ESR rule:
db.people.createIndex({name: 1, email: 1, age: 1})
For an efficient query:
db.people.find({name: "Sam", age: {$lt: 30}}).sort({email: 1})
Two Birds, One Stone
Compound indexes can satisfy simpler queries without needing separate indexes. For example:
db.people.createIndex({name: 1, email: 1, age: 1})
also covers:
db.people.createIndex({name: 1})
No need for a separate index on name alone.
Compound Index Performance
Indexes enhance read performance but can slow down writes, especially if not carefully managed. Cardinality, or the number of unique values, significantly affects index efficiency.
Cardinality Matters
Fields with low cardinality like gender or boolean flags don't benefit much from being indexed due to minimal value variety.
The ESR Rule
The ESR rule outlines that fields should be ordered by equality, sorting, and then range operations. In:
db.people.createIndex({name: 1, email: 1, age: 1})
a query should start with equality on name, followed by sorting email, and then applying range on age.
Conclusion
Compound indexes are powerful tools in MongoDB for handling complex queries efficiently. Adhering to the ESR rule and optimal field cardinality ensures maximum performance. Remember, the order and prefixes play key roles in compound index behavior.
FAQ
Why is the order of fields important in a compound index?
The order determines the index's prefix structure, affecting query efficiency. Queries use prefixes to navigate the index, so proper field arrangement helps optimize query performance.
What is a B-Tree in MongoDB indexes?
A B-Tree is a data structure used by MongoDB indexes to store and sort data efficiently. It allows quick searches and updates, making queries much faster compared to scanning an entire collection.
Can compound indexes include fields with low cardinality?
They can, but it's usually inefficient. Low cardinality results in less effective indexing, as the variety of data is minimal, reducing the benefit of quicker lookups.
