After the sorted operation OOM incident in production from the previous post, I realized my understanding of MongoDB indexes wasn’t as deep as it should be. I started searching extensively and finally organized some key points. Some were covered in the previous post — feel free to review MongoDB Sorted Operation OOM.
When to Use Indexes
- When you have sorting scenarios and in-memory sorting exceeds 32 MB.
- When a specific field has a uniqueness requirement.
- When the document count is large.
- Build indexes on high-cardinality fields.
When NOT to Use Indexes
- When an index cannot effectively filter data.
- Don’t set indexes on frequently updated fields.
How to Design and Use Indexes
Use Compound Indexes Instead of Single Field Indexes
In MongoDB, a find query can only use one index at a time (in most scenarios). So if your use case frequently filters on multiple fields, use a Compound Index composed of multiple fields to match your query conditions.
Follow the ESR Rule
ESR stands for Equality, Sort, Range. This means the index field order must follow this sequence for optimal efficiency:
- Equality: Fields used in equality queries.
- Sort: Fields used for sorting.
- Range: Fields used for range queries.
Let Indexes Cover Your Queries
Covered Queries can return results directly from the index, making them highly efficient.
The following conditions must be met:
- All query condition fields must be part of the index.
- All returned fields must be part of the index.
- The query conditions must not include equality to null, e.g.,
(i.e. {"field" : null} or {"field" : {$eq : null}} ).
Be Careful with Indexes on Low-Cardinality Fields
Querying fields with few unique values returns large result sets.
Compound indexes can include low-cardinality fields, but the combined index should also include high-cardinality fields.
Remove Unnecessary Indexes
Too many indexes degrade CUD (Create, Update, Delete) performance. Keep indexes to an appropriate number — don’t create too many.
Don’t Use Wildcard Indexes as Replacements
Wildcard indexes provide additional flexibility for special query patterns or polymorphic document workloads, but like other indexes, they require storage and maintenance, adding overhead to MongoDB.
If your application’s query patterns are known in advance, use more selective indexes on the specific fields your queries access.
Use Partial Indexes
You can reduce index size and performance overhead by building Partial Indexes on specific fields. For example, on an order status field, only index documents with “processing” status.
Build Appropriate Indexes for Sorting
Since MongoDB can only use up to 32 MB of memory for sorting, you need to build indexes on the relevant fields to avoid this limitation.
Be Careful with These Operators
- $where and $exists: These operators completely bypass indexes.
- $ne and $not: Can generally still use indexes, but with very low efficiency — may even fall back to COLLSCAN.
- If your query uses $or, ensure each condition in the $or can use a different index (under $or, multiple index references are supported). However, $or + $sort will never use an index. If possible, use range queries instead to avoid $or + $sort.
- $nin: The “not in” operator also easily falls back to COLLSCAN.
- For indexes within pipelines, be careful — once data passes through $project, $group, $lookup, $unwind, etc., indexes become completely unusable.
Don’t Build Indexes on Frequently Written Fields
Building indexes on frequently updated fields means every write or update also needs to update the index B-tree, degrading performance. Indexes perform best in read-heavy scenarios.
Automated Index Recommendations
If you’re unsure how to design and use indexes, you can use Performance Advisor to monitor queries exceeding 100ms and automatically suggest new indexes to improve performance. However, this isn’t the recommended approach.
The better practice is to plan ahead and use Performance Advisor to identify areas that may need additional tuning.
Conclusion
The above covers the most common scenarios for designing and using indexes. If you have other common scenarios and corresponding index usage patterns, feel free to leave a comment and let’s discuss.
Feel free to leave a comment on my blog. Your feedback motivates me to keep writing. Thank you for reading, and let’s grow together to become better versions of ourselves.
