How to Optimize NestJS MongoDB Queries with Composite Indexes

As your database grows from thousands of documents to millions, queries that once resolved in milliseconds start to lag, hog CPU threads, and block request pipelines. If you use MongoDB with NestJS and Mongoose, building intelligent indexes is the single most impactful action you can take to keep operations scalable.

Let's dissect how MongoDB indexes work under the hood and how to implement composite indexing in NestJS.

1. What is a Composite Index?

A single field index (e.g., indexing just the email field) works well when you only filter by email. However, if your query filters by multiple fields—for example, looking up a student's attendance history for a specific course:

typescript
// Query filters on both courseId AND studentId
this.attendanceModel.find({ courseId, studentId }).sort({ date: -1 });

A single-field index on courseId alone is insufficient. MongoDB must first locate all documents for that course, then scan through them to filter by student roll number, and finally sort them in memory.

A Composite Index indexes multiple fields together in a structured tree layout.

2. Implementing in NestJS Mongoose

In a NestJS application, composite indexes are defined directly in the Mongoose schema decorator. Here is how you configure a compound index on courseId, studentId, and date:

typescript
import { Schema, Prop, SchemaFactory } from '@nestjs/mongoose';
import { Document, Types } from 'mongoose';

@Schema({ timestamps: true })
export class Attendance extends Document {
  @Prop({ type: Types.ObjectId, required: true })
  courseId: Types.ObjectId;

  @Prop({ type: String, required: true })
  studentId: string;

  @Prop({ type: Date, required: true })
  date: Date;

  @Prop({ type: String, enum: ['Present', 'Absent'], required: true })
  status: string;
}

export const AttendanceSchema = SchemaFactory.createForClass(Attendance);

// Define the compound index (1 for ascending, -1 for descending)
AttendanceSchema.index({ courseId: 1, studentId: 1, date: -1 });

3. The ESR Rule (Equality, Sort, Range)

When structuring composite indexes, always order the fields according to the ESR Rule:

1. Equality: Place fields you match on exact values first (e.g., courseId: 1).

2. Sort: Place fields you use to order results second (e.g., date: -1).

3. Range: Place fields that require range checks ($gt, $lt, $in) last.

Following this rule guarantees that MongoDB traverses the indexes sequentially without having to perform an expensive memory sorting operation.

Verify query efficiency in your logs using explain('executionStats') to ensure the database performs an IXSCAN instead of a COLLSCAN.