Have you ever noticed that some MongoDB queries slow down when filtering on more than one field? In our previous discussion, we looked at single-field indexes. While those are great for simple lookups, they often fall short when your queries need to be filtered or sort by multiple fields. In this article, we’ll walk through a real-world scenario, explain the limitations of single-field indexes, and show you how compound indexes can solve the problem.
A Quick Real-World Scenario
Imagine you’re building an application that stores user data. You might need to filter users based on several criteria. For example, you may want to retrieve users located in Gdansk. Additionally, you might filter by email addresses ending in @example.com, an active account status, and a signup date on or after January 1, 2024.
|
1 2 3 4 5 6 |
await this.userModel.find({ location: ‘Gdansk’, email: ‘user@example.com’, accountStatus: ‘active’, signupDate: { $gte: new Date(‘2024-01-01’) } }); |
Although this query filters on four fields, the performance-critical part is often filtered by location and email.
If you only have a single-field index on location, MongoDB would first narrow down the documents by city and then apply the other filters one by one, which can be slow when dealing with millions of records.
The solution: compound index
A compound index is an index that includes multiple fields from a document in a specified order. This order is crucial because it determines which queries can fully leverage the index. With a compound index, MongoDB can apply several filters in a single, efficient lookup.
Why Use Compound Indexes?
- Multi-Field Filtering: They allow MongoDB to filter by several fields simultaneously.
- Optimized Sorting: When queries require sorting by multiple fields, compound indexes can support both filtering and sorting.
- Improved Performance: By narrowing down the number of documents early, compound indexes significantly reduce query execution time.
Difference Between Single-Field and Compound Indexes
Single-Field Index:
An index on one field (e.g.,
{ age: 1 }) works well for queries filtering solely by that field:
|
1 |
await this.userModel.find({ age: { $gte: 30 } }); |
However, if your query adds additional filters such as email or accountStatus, a single-field index may not fully optimize it.
An index that includes multiple fields like
{ age: 1, email: 1 } is designed to handle queries that filter on both fields efficiently:
|
1 2 3 4 |
await this.userModel.find({ age: { $gte: 30 }, email: ‘user@example.com’ }); |
This compound index enables MongoDB to locate matching documents faster by applying both filters in a single index lookup.
Stack requirments
This article demonstrates examples using the following technologies:
- Backend: NestJS with TypeScript and Mongoose
- Database: A Dockerized MongoDB instance
- Repository: The code for this article can be found here.
Implementing Compound indexes in NestJS with Moongoose
Below is an updated version of your existing User schema. It contains a compound index on the age and email fields. We can add it using the index method built into Mongoose.
user.schema.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
import { Prop, Schema, SchemaFactory } from ‘@nestjs/mongoose’; import { Document } from ‘mongoose’; export type UserDocument = User & Document; @Schema({ timestamps: true }) export class User { @Prop({ required: true }) name: string; @Prop({ required: true, unique: true }) email: string; @Prop({ required: true }) location: string; @Prop({ required: true }) city: string; @Prop({ required: true }) age: number; @Prop({ required: true }) accountStatus: string; @Prop() membershipType?: string; @Prop() signupDate?: Date; } export const UserSchema = SchemaFactory.createForClass(User); UserSchema.index({ age: 1, email: 1 }); UserSchema.index({ location: 1, age: –1 }); |
How these indexes help
Index { age: 1, email: 1 }
- Optimizes queries that filter by age or by both age and email.
- Supports sorting by age and then by email (if needed).
- Directly benefits queries that use these two fields as primary filters.
Index { location: 1, age: –1 }
Ideal for queries filtering by location and sorting results by age in descending order.
Example usage:
|
1 2 |
await this.userModel.find({ location: ‘Gdansk’ }) .sort({ age: –1 }); |
Using
{ location: 1, age: –1 } ensures that when filtering by
location, the results are also sorted by age from highest to lowest.
For more complex queries, consider using a compound index that covers all critical fields. Always design indexes based on the most frequent and performance-critical query patterns in your application.
Additional Indexing Strategies
Sorting with Compound Indexes
If queries need to sort users first by location and then by email, our existing compound index { location: 1, email: 1 } can optimize this operation:
|
1 2 3 4 5 |
await this.userModel.find() .sort({ location: 1, email: 1 }); |
However, sorting by email first and then location requires a different index { email: 1, location: 1 }, which is not covered by the previous index.
Partial Indexes for Optimization
If some fields aren’t present in every document, a partial index can reduce the index size and improve performance:
|
1 2 3 4 5 6 7 8 |
UserSchema.index( { location: 1, email: 1 }, { partialFilterExpression: { location: { $exists: true } } } ); |
This reduces the index size and speeds up queries that frequently filter by location.
Other data optimization techniques
Apart from indexes, there are other ways to optimize data storage and queries in MongoDB:
- Efficient Schema Design: Embed small related documents to reduce joins, reference large shared objects.
- Sharding: Distribute large datasets across multiple shards using an even-distributed shard key.
- Aggregation Optimization: Use $match early, $project to minimize memory usage, and optimize $lookup operations.
- Write Performance: Use bulkWrite() for batch inserts, tune WriteConcern, and minimize indexed field updates.
- Caching: Store frequently accessed query results in Redis or in-memory storage to reduce database load.
Summary
Compound indexes are a powerful tool to optimize MongoDB queries involving multiple fields. By strategically choosing index orders and combinations, you can significantly improve query performance. In NestJS with Mongoose, adding compound indexes is pretty straightforward using the .index() method in schema definitions.
This article provides some basic examples of data optimization. For more details, I highly recommend visiting the official MongoDB documentation
Leave a Reply