Why is my query still slow with an index?

Check explain: the index might not match the filter and sort order, the query might return a very large result, a regex might not use the index, or an in-memory sort might be happening. Match the ESR rule and examine totalDocsExamined.

MongoDB Module 6: Indexes and Query Performance

Introduction

Without an index, MongoDB reads every document in a collection to answer a query (a collection scan). Indexes let it jump straight to matching documents. This module shows how to create the right indexes and how to prove they are used.

Prerequisites

Module 5 completed

Step-by-Step Guide

Step 1: Create single-field and unique indexes

Every collection has an index on _id. Add indexes on fields you filter or sort by. A unique index also enforces uniqueness, for example of an email address.

db.customers.createIndex({ email: 1 }, { unique: true })
db.orders.createIndex({ status: 1 })
db.orders.getIndexes()

Step 2: Build compound indexes with the ESR rule

A compound index covers several fields in a set order. A good rule for ordering is ESR: Equality fields first, then Sort fields, then Range fields. The index can then serve the filter and the sort without a separate in-memory sort. Prefix rule: an index on {a, b, c} also serves queries on a, and on a and b.

// query: status equals, sorted by placedAt, with a range on total
db.orders.createIndex({ status: 1, placedAt: -1, total: 1 })

db.orders.find({ status: "shipped", total: { $gt: 20 } }).sort({ placedAt: -1 })

Step 3: Use multikey, partial, and TTL indexes

An index on an array field becomes a multikey index with one entry per element. A partial index only covers documents matching a condition, which saves space. A TTL index deletes documents automatically after a time, useful for sessions and temporary data.

db.orders.createIndex({ "items.sku": 1 })                       // multikey
db.orders.createIndex({ placedAt: 1 }, { partialFilterExpression: { status: "pending" } })
db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })   // TTL: 1 hour

Step 4: Read explain output

explain with executionStats shows how a query ran. An IXSCAN stage means an index was used, while COLLSCAN means a full scan. Compare totalDocsExamined and totalKeysExamined with nReturned: a healthy query examines roughly as many documents as it returns.

db.orders.find({ status: "shipped" }).sort({ placedAt: -1 }).explain("executionStats")

// look at:
//   winningPlan.stage          IXSCAN or COLLSCAN
//   executionStats.nReturned
//   executionStats.totalDocsExamined
//   executionStats.executionTimeMillis

Step 5: Index with care

Every index speeds reads but slows writes and uses memory and disk, so do not index everything. Remove indexes that are never used (the $indexStats stage shows usage), and keep the indexes you do use in memory. Test index changes on realistic data, and in Atlas use the Performance Advisor to see suggested indexes.

db.orders.aggregate([ { $indexStats: {} } ])
db.orders.dropIndex("status_1")

FAQs

Why is my query still slow with an index?: Check explain: the index might not match the filter and sort order, the query might return a very large result, a regex might not use the index, or an in-memory sort might be happening. Match the ESR rule and examine totalDocsExamined.

Summary

Index the fields you filter and sort on, order compound indexes by equality, sort, then range, use multikey, partial, and TTL indexes where they fit, verify with explain, and drop unused indexes.