mongodb-query-and-index-optimize

To fix a slow MongoDB query showing COLLSCAN, you need to design a compound index applying the ESR rule and prefix principle, replacing collection scans with IXSCAN. This ensures the index field ordering matches your query shapes and eliminates inefficient in-memory sorts.

Optimize a MongoDB aggregation pipeline by moving $match and $project stages early to reduce document flow, controlling $unwind on large arrays to prevent document explosion, and tuning $lookup and $graphLookup stages to manage memory spill behavior efficiently.

Use partial or sparse indexes in MongoDB when your workload queries a subset of documents meeting specific criteria, reducing index size and maintenance overhead. Specialized index selection also includes TTL, geospatial, text, hashed, wildcard, and multikey indexes tailored to real workload needs.

The best way to diagnose missing or unused indexes in MongoDB is by analyzing executionStats from explain, reviewing $indexStats, and monitoring the profiler and slow query logs. This validates scan-to-return inefficiency and identifies indexes that do not serve current query patterns.

Safely build indexes in production MongoDB by planning the rollout to minimize performance impact, using hidden indexes to test query plans before exposure, and verifying improvements through executionStats, plan cache, and MongoDB 8.0 query settings.

A MongoDB query still performs an in-memory SORT when the compound index field ordering does not align with the query and sort criteria. Correcting the index design using the ESR rule and prefix principle ensures the index can return documents in the required sorted order.