Optimizing Query Performance
1. Analyzing Query Performance
| Tool | Detail |
|---|---|
| explain() | Plan analysis |
| profiler | Captures slow ops |
| Atlas Performance Advisor | Index suggestions |
2. Understanding Explain Plans
| Mode | Detail |
|---|---|
| queryPlanner | Chosen + rejected plans |
| executionStats | nReturned, totalKeysExamined, totalDocsExamined |
| allPlansExecution | Stats for all candidate plans |
3. Using Covered Queries
| Condition | Detail |
|---|---|
| Projection | Only indexed fields, exclude _id |
| Predicate | All filter fields in index |
| Effect | totalDocsExamined = 0 |
4. Avoiding Collection Scans
| Symptom | Fix |
|---|---|
| stage = COLLSCAN | Add supporting index |
| Regex w/o anchor | Use prefix anchor or text index |
5. Optimizing Index Selection
| Aspect | Detail |
|---|---|
| ESR rule | Equality → Sort → Range field order |
| hint() | Force index when planner chooses poorly |
6. Using Index Intersection
| Aspect | Detail |
|---|---|
| Mechanism | Combines bounds from two indexes |
| Caveat | Usually slower than a well-chosen compound index |
7. Understanding Query Selectivity
| Aspect | Detail |
|---|---|
| Selective | Returns small fraction → index efficient |
| Non-selective | Returns most docs → scan may be faster |
8. Optimizing Sort Operations
| Form | Detail |
|---|---|
| Indexed sort | Stage SORT absent in explain |
| Blocking SORT | Limited by 100MB (use allowDiskUse) |
9. Using Projection to Reduce Data
| Benefit | Detail |
|---|---|
| Network | Less bytes transferred |
| Covered query | Enables index-only execution |
10. Implementing Query Caching Strategies
| Layer | Detail |
|---|---|
| Plan cache | Reuses query plans (clearable) |
| App cache | Redis / in-process for hot reads |
| Materialized views | $merge into pre-aggregated collection |
11. Understanding Query Targeting
| Cluster | Detail |
|---|---|
| Targeted | Shard key in filter → single shard |
| Broadcast | Scatter-gather to all shards |
| Tip | Include shard key whenever possible |