MongoDB Query and Index Optimization

Query patterns and indexing strategies for MongoDB, maintained by MongoDB. Contains 46 rules across 5 categories, prioritized by impact. Includes MongoDB 8.0 features: bulkWrite command, $queryStats (introduced in MongoDB 6.0.7, with 8.1/8.2 enhancements), Query Settings, and updateOne sort option. Indexes are the primary tool for query performance—most slow queries are missing an appropriate index.

When to Apply

Reference these guidelines when:

• Writing new MongoDB queries or aggregations

• Creating or reviewing indexes for collections

• Debugging slow queries (COLLSCAN, high execution time)

• Reviewing explain() output

• Seeing Performance Advisor suggestions

• Optimizing aggregation pipelines

• Implementing built-in $text search with text indexes

• Adding geospatial queries

• Setting up TTL (time-to-live) for data expiration

• Analyzing index usage with $indexStats

• Profiling slow operations

Use mongodb-search instead when the request is about Atlas Search, $search , $searchMeta , analyzers, synonyms, autocomplete, or search-specific relevance tuning on Atlas-hosted data.

Rule Categories by Priority

Priority Category Impact Prefix Rules

1 Index Essentials CRITICAL index-

9

2 Specialized Indexes HIGH index-

11

3 Query Patterns HIGH query-

10

4 Aggregation Optimization HIGH agg-

8

5 Performance Diagnostics MEDIUM perf-

8

Quick Reference

1. Index Essentials (CRITICAL) - 9 rules

• index-compound-field-order

• Equality first, sort second, range last (ESR rule)

• index-compound-multi-field

• Use compound indexes for multi-field queries

• index-ensure-usage

• Avoid COLLSCAN, verify with explain()

• index-remove-unused

• Audit indexes with $indexStats

• index-high-cardinality-first

• Put selective fields at index start

• index-covered-queries

• Include projected fields to avoid document fetch

• index-prefix-principle

• Compound indexes serve prefix queries

• index-creation-background

• Build indexes without blocking operations

• index-size-considerations

• Keep indexes in RAM for optimal performance

2. Specialized Indexes (HIGH) - 11 rules

• index-unique

• Enforce uniqueness for identifiers and constraints

• index-partial

• Index subset of documents to reduce size

• index-sparse

• Skip documents missing the indexed field

• index-ttl

• Automatic document expiration for sessions/logs

• index-text-search

• Built-in $text search with stemming and relevance

• index-wildcard

• Dynamic field indexing for polymorphic schemas

• index-multikey

• Array field indexing (one entry per element)

• index-geospatial

• 2dsphere indexes for location queries

• index-hashed

• Uniform distribution for equality lookups or shard keys

• index-clustered

• Ordered storage with clustered collections

• index-hidden

• Safely test index removals in production

3. Query Patterns (HIGH) - 10 rules

• query-use-projection

• Fetch only needed fields

• query-avoid-ne-nin

• Use $in instead of negation operators

• query-or-index

• All $or clauses must have indexes for index usage

• query-anchored-regex

• Start regex with ^ for index usage

• query-batch-operations

• Avoid N+1 patterns, use $in or $lookup

• query-pagination

• Use range-based pagination, not skip

• query-exists-with-sparse

• Understand $exists behavior with sparse indexes

• query-sort-collation

• Match sort order and collation to indexes

• query-bulkwrite-command

• MongoDB 8.0 cross-collection atomic batch operations

• query-updateone-sort

• MongoDB 8.0 deterministic updates with sort option

4. Aggregation Optimization (HIGH) - 8 rules

• agg-match-early

• Filter with $match at pipeline start

• agg-project-early

• Reduce document size with $project

• agg-sort-limit

• Combine $sort with $limit for top-N

• agg-lookup-index

• Ensure $lookup foreign field is indexed

• agg-graphlookup

• Use $graphLookup for recursive graph traversal

• agg-avoid-large-unwind

• Don't $unwind massive arrays

• agg-allowdiskuse

• Handle large aggregations exceeding 100MB

• agg-group-memory-limit

• Control $group memory and spills

5. Performance Diagnostics (MEDIUM) - 8 rules

• perf-explain-interpretation

• Read explain() output like a pro

• perf-slow-query-log

• Use profiler to find slow operations

• perf-index-stats

• Find unused indexes with $indexStats

• perf-query-plan-cache

• Understand and manage query plan cache

• perf-use-hint

• Force a known-good index when the optimizer errs

• perf-atlas-performance-advisor

• Use Atlas suggestions for missing indexes

• perf-query-stats

• Workload-based query analysis with $queryStats (introduced in MongoDB 6.0.7, with 8.1/8.2 enhancements)

• perf-query-settings

• MongoDB 8.0 persistent index hints with setQuerySettings

Key Principle

"If there's no index, it's a collection scan."

Every query without a supporting index scans the entire collection. A 10ms query on 10,000 documents becomes a 10-second query on 10 million documents.

ESR Rule (Equality-Sort-Range)

The most important rule for compound index field order:

// Query: status = "active" AND createdAt > lastWeek ORDER BY priority // ESR: Equality (status) → Sort (priority) → Range (createdAt) db.tasks.createIndex({ status: 1, priority: 1, createdAt: 1 })

Position Type Example Why

First Equality status: "active"

Narrows to exact matches

Second Sort ORDER BY priority

Avoids in-memory sort

Third Range createdAt > date

Scans within sorted data

ERS Exception: When range predicate is highly selective, placing Range before Sort reduces sort input. Verify with explain().

How to Use

Read individual rule files for detailed explanations and code examples:

rules/index-compound-field-order.md rules/perf-explain-interpretation.md rules/_sections.md

Each rule file contains:

• Brief explanation of why it matters

• Incorrect code example with explanation

• Correct code example with explanation

• "When NOT to use" exceptions

• How to verify with explain()

• Performance impact and metrics

How These Rules Work

Recommendations with Verification

Every rule in this skill provides:

• A recommendation based on best practices

• A verification checklist of things that should be confirmed

• Commands to verify so you can check before implementing

• MCP integration for automatic verification when connected

Why Verification Matters

I analyze code patterns, but I can't see your actual database without a connection. This means I might suggest:

• Creating an index that already exists

• Optimizing a query that's already using an efficient index

• Adding a compound index when a prefix already covers the query

Always verify before implementing. Each rule includes verification commands.

MongoDB MCP Integration

For automatic verification, connect the MongoDB MCP Server:

Option 1: Connection String

{ "mcpServers": { "mongodb": { "command": "npx", "args": ["-y", "mongodb-mcp-server", "--readOnly"], "env": { "MDB_MCP_CONNECTION_STRING": "mongodb+srv://user:pass@cluster.mongodb.net/mydb" } } } }

Option 2: Local MongoDB

{ "mcpServers": { "mongodb": { "command": "npx", "args": ["-y", "mongodb-mcp-server", "--readOnly"], "env": { "MDB_MCP_CONNECTION_STRING": "mongodb://localhost:27017/mydb" } } } }

⚠️ Security: Use --readOnly for safety. Remove only if you need write operations.

When connected, I can automatically:

• Check existing indexes via mcp__mongodb__collection-indexes

• Analyze query performance via mcp__mongodb__explain

• Verify data patterns via mcp__mongodb__aggregate

⚠️ Action Policy

I will NEVER execute write operations without your explicit approval.

Operation Type MCP Tools Action

Read (Safe) find , aggregate , explain , collection-indexes , $indexStats

I may run automatically to verify

Write (Requires Approval) create-index , drop-index , update-many , delete-many

I will show the command and wait for your "yes"

Destructive (Requires Approval) drop-collection , drop-database

I will warn you and require explicit confirmation

When I recommend creating an index or making changes:

• I'll explain what I want to do and why

• I'll show you the exact command

• I'll wait for your approval before executing

• If you say "go ahead" or "yes", only then will I run it

Your database, your decision. I'm here to advise, not to act unilaterally.

Working Together

If you're not sure about a recommendation:

• Run the verification commands I provide

• Share the output with me

• I'll adjust my recommendation based on your actual data

We're a team—let's get this right together.

Full Compiled Document

For the complete guide with all rules expanded: AGENTS.md