MongoDB Index Optimization: Compound Indexes and Query Plan Analysis

Introduction

MongoDB's indexing system is flexible but requires careful thought to use effectively. Unlike relational databases where query planners are relatively predictable, MongoDB's query planner can make surprising choices when indexes are not designed correctly. I learned this the hard way when a simple query on a 10 million document collection started taking over 5 seconds after a schema change.

The problem was a missing compound index, but the solution required understanding how MongoDB uses indexes for multi-field queries, sort operations, and range conditions. This post covers the analysis process and the index optimization strategy that brought the query time down to 12 milliseconds.

Environment

• MongoDB: 7.0.4 (replica set, 3 nodes)
• Collection: trades (10 million documents, ~15GB)
• Document structure: Trade records with timestamp, symbol, strategy, and P&L fields
• Query pattern: Filter by symbol and strategy, sort by timestamp, with date range

Problem

The query that was performing poorly was:

db.trades.find({
    symbol: "AAPL",
    strategy: "momentum",
    timestamp: {
        $gte: ISODate("2024-07-01"),
        $lte: ISODate("2024-07-31")
    }
}).sort({ timestamp: -1 }).limit(100)

This query was part of the dashboard that displays recent trades for a given strategy. It should be fast because it filters to a specific symbol and strategy. Instead, it was taking 5-8 seconds.

I ran explain() to understand the query plan:

db.trades.find({
    symbol: "AAPL",
    strategy: "momentum",
    timestamp: {
        $gte: ISODate("2024-07-01"),
        $lte: ISODate("2024-07-31")
    }
}).sort({ timestamp: -1 }).limit(100).explain("executionStats")

The output revealed:

{
    "queryPlan": {
        "planVersion": 2,
        "winningPlan": {
            "stage": "SORT",
            "sortPattern": { "timestamp": -1 },
            "inputPlan": {
                "stage": "FETCH",
                "inputPlan": {
                    "stage": "IXSCAN",
                    "keyPattern": { "timestamp": 1 },
                    "indexName": "timestamp_1",
                    "direction": "BACKWARD"
                }
            }
        }
    },
    "executionStats": {
        "totalDocsExamined": 8547293,
        "totalKeysExamined": 8547293,
        "executionTimeMillis": 5847
    }
}

The query planner chose the timestamp_1 index (a single-field index on timestamp) and scanned over 8.5 million documents. It was using the index for the sort but not for the filter on symbol and strategy. This meant MongoDB had to fetch every document that matched the timestamp range and then filter by symbol and strategy in memory.

Analysis

The existing indexes on the trades collection were:

db.trades.getIndexes()

[
    { "v": 2, "key": { "_id": 1 }, "name": "_id_" },
    { "v": 2, "key": { "timestamp": 1 }, "name": "timestamp_1" },
    { "v": 2, "key": { "symbol": 1 }, "name": "symbol_1" },
    { "v": 2, "key": { "strategy": 1 }, "name": "strategy_1" }
]

There were three single-field indexes but no compound index that covered the query. MongoDB's query planner evaluates which index to use based on selectivity. The symbol index would narrow down to about 2 million documents (AAPL trades), and the strategy index would narrow down further. But no single index covered both the filter and the sort efficiently.

The issue was the sort. MongoDB can use an index for both filtering and sorting only if the index fields are in the correct order. For this query, the optimal index would be:

1. symbol (equality filter — most selective)
2. strategy (equality filter — second most selective)
3. timestamp (range filter + sort)

This order allows MongoDB to use the index for the equality filters first, then scan the matching documents in timestamp order for the range and sort.

I verified the selectivity of each field:

db.trades.distinct("symbol").length  // 500 unique symbols
db.trades.distinct("strategy").length  // 20 unique strategies

With 500 symbols and 20 strategies, the symbol field is the most selective, followed by strategy.

Solution

I created a compound index that matches the query pattern:

db.trades.createIndex(
    { symbol: 1, strategy: 1, timestamp: -1 },
    { name: "symbol_strategy_timestamp" }
)

The field order matters:

• symbol: 1 — Equality filter, most selective
• strategy: 1 — Equality filter, second most selective
• timestamp: -1 — Range filter and sort, matches the query's sort direction

After creating the index, I re-ran the explain:

db.trades.find({
    symbol: "AAPL",
    strategy: "momentum",
    timestamp: {
        $gte: ISODate("2024-07-01"),
        $lte: ISODate("2024-07-31")
    }
}).sort({ timestamp: -1 }).limit(100).explain("executionStats")

{
    "queryPlan": {
        "winningPlan": {
            "stage": "FETCH",
            "inputPlan": {
                "stage": "IXSCAN",
                "keyPattern": { "symbol": 1, "strategy": 1, "timestamp": -1 },
                "indexName": "symbol_strategy_timestamp",
                "direction": "BACKWARD",
                "indexBounds": {
                    "symbol": ["[\"AAPL\", \"AAPL\"]"],
                    "strategy": ["[\"momentum\", \"momentum\"]"],
                    "timestamp": ["[new Date(1719792000000), new Date(1722470400000)]"]
                }
            }
        }
    },
    "executionStats": {
        "totalDocsExamined": 156,
        "totalKeysExamined": 156,
        "executionTimeMillis": 12
    }
}

The results were dramatic:

• Documents examined: 8,547,293 -> 156
• Execution time: 5,847ms -> 12ms

The new index was used for both filtering and sorting. MongoDB scanned only the 156 documents that matched all three criteria.

I also added a TTL index for automatic cleanup of old trades:

db.trades.createIndex(
    { timestamp: 1 },
    {
        name: "trades_ttl",
        expireAfterSeconds: 7776000  // 90 days
    }
)

And created indexes for other common query patterns:

// For queries filtering by strategy only
db.trades.createIndex({ strategy: 1, timestamp: -1 });

// For aggregation pipelines grouping by symbol and date
db.trades.createIndex({ symbol: 1, timestamp: 1, pnl: 1 });

To monitor index usage over time, I ran:

db.trades.aggregate([
    { $indexStats: {} }
]).forEach(printjson);

This shows which indexes are being used and their access counts, helping identify unused indexes that can be dropped.

Lessons Learned

1. Compound index field order matters — Place equality fields first, then sort fields, then range fields. This order matches how MongoDB uses indexes.
2. Use explain() with executionStats — The executionStats mode shows exactly how many documents were examined and how long the query took. Compare before and after index changes.
3. Match sort direction in the index — If your query sorts descending, create the index with descending fields. This avoids an in-memory sort stage.
4. Monitor index usage — Use $indexStats to identify unused indexes. Unused indexes waste storage and slow down writes.
5. Consider覆盖索引 (covered queries) — When an index contains all fields needed by a query, MongoDB can answer the query entirely from the index without fetching documents.

This blog does not accept any external sponsorships, affiliate marketing, or ad revenue.