Sub-50ms MongoDB Query Optimization: Indexing Strategies & Aggregation Pipelines

Diagnosing Query Bottlenecks with `.explain("executionStats")` In high-volume applications, a single missing index can cascade into full collection scans (`COLLSCAN`), seizing CPU cores and driving disk I/O to 100%.

The Equality, Sort, Range (ESR) Rule When building compound indexes, always follow the **ESR Rule**: 1. **Equality**: Place fields with exact matches first (e.g., `organizationId`, `status`). 2. **Sort**: Place fields used in the sort clause next (e.g., `createdAt: -1`). 3. **Range**: Place range filter fields last (e.g., `price: { $gte: 100 }`).

// Schema Indexing Implementation
appointmentSchema.index(
  { tenantId: 1, status: 1, appointmentDate: -1 },
  { name: 'idx_tenant_status_date', background: true }

// High-Performance Covered Query Pipeline const activeAppointments = await Appointment.find({ tenantId: req.tenantId, status: 'CONFIRMED', appointmentDate: { $gte: new Date() } }) .sort({ appointmentDate: 1 }) .select('patientName appointmentDate doctorId status') // Covered projection .lean() // Bypass Mongoose hydration overhead for 3x speedup .exec(); ```

Production Rules - **Always use `.lean()`** for read-only queries to avoid Mongoose document instantiation overhead. - **Index Intersection is NOT a substitute** for a well-designed compound index. - **Monitor the 100MB RAM Sort Limit**: Unindexed sorts exceeding 100MB throw an error unless `allowDiskUse: true` is flagged, which introduces massive latency.