Going Deeper: Production MongoDB›Extra· 45 min read

Indexes & explain() — Making Queries Fast

Why your queries get slow as data grows, and how an index turns a full-collection scan into an instant lookup.

What you will learn

Explain what an index is and why it speeds up queries
Create single-field, compound, unique, text and TTL indexes
Read explain() output and tell a COLLSCAN from an IXSCAN

The problem: scanning every document

When you run db.users.find({ email: "asha@x.com" }), how does MongoDB find that one user? With no help, it checks every single document in the collection one by one — this is called a collection scan (COLLSCAN). With 50 documents that is instant. With 5 million, it is painfully slow. Real apps have lots of data, so this matters a great deal.

An index is a separate, sorted lookup structure that MongoDB keeps for a field — exactly like the index at the back of a textbook. Instead of reading the whole book to find “photosynthesis”, you flip to the index, see it is on page 214, and jump straight there. An index on email lets MongoDB jump straight to the matching document instead of scanning them all.

Note: A scan that uses an index is called an IXSCAN (index scan). Your goal as a developer is to turn slow COLLSCAN queries into fast IXSCAN queries by adding the right indexes.

Creating indexes

You build an index with createIndex, passing the field(s) and a direction (1 ascending, -1 descending — for a single-field index the direction rarely matters). Here are the common kinds:

The index types you will use most

// Single-field index on email
db.users.createIndex({ email: 1 })

// Compound index: city first, then age
db.users.createIndex({ city: 1, age: -1 })

// Unique index: no two users can share an email
db.users.createIndex({ email: 1 }, { unique: true })

// Text index: enables word searches on a field
db.posts.createIndex({ title: "text" })

// TTL index: auto-delete sessions 1 hour after createdAt
db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })

Walking through each one. A single-field index on email speeds up any query that filters by email. A compound index on { city: 1, age: -1 } covers queries that filter by city (and optionally also age) — the order matters, and it works like sorting a class list first by city, then by age within each city. A unique index does double duty: it speeds lookups *and* enforces that no two documents share that value (perfect for emails and usernames). A text index lets you run word searches with $text. A TTL index (Time To Live) automatically deletes documents a set number of seconds after a date field — ideal for sessions, OTPs and temporary data that should expire on their own.

Note: Output (from a createIndex call): email_1 MongoDB confirms the index by returning its auto-generated name (field name + direction). Once built, every matching query can use it.

Reading the plan with explain()

How do you know whether a query actually *used* an index? You ask MongoDB to explain its plan. Chain .explain("executionStats") onto a query and it reports how it ran — including the all-important stage and how many documents it had to examine.

Ask MongoDB how it ran the query

db.users.find({ email: "asha@x.com" }).explain("executionStats")

You do not need to read the whole report — just two fields tell the story. Look at the winning plan’s stage and at totalDocsExamined (how many documents MongoDB had to look at to answer).

Note: Output (trimmed) — WITHOUT an index on email: stage: COLLSCAN, totalDocsExamined: 1000000 Output (trimmed) — WITH the index on email: stage: IXSCAN, totalDocsExamined: 1 Same query, same data: the scan looked at a million documents, the index looked at one. That is the difference between a slow page and an instant one.

Here is the everyday workflow for fixing a slow query:

Notice a query feels slow (a list page, a search box, a report).
Run that exact query with .explain("executionStats").
If the stage is COLLSCAN and totalDocsExamined is huge, MongoDB is scanning everything.
Create an index with createIndex on the field(s) the query filters or sorts by.
Re-run explain and confirm the stage is now IXSCAN and totalDocsExamined has dropped.

Watch out: Indexes are not free. Every index takes disk space and makes writes (insert/update/delete) a little slower, because MongoDB must keep the index up to date. So index the fields you actually query and sort by — do not blindly index everything.

Tip: In Mongoose you add indexes in the schema, e.g. email: { type: String, unique: true, index: true }, or userSchema.index({ city: 1, age: -1 }). Same idea, declared where you define the model.

Q. In explain() output, which stage means MongoDB scanned the whole collection?

Answer: COLLSCAN = collection scan (every document examined, slow). IXSCAN means an index was used to jump straight to matches (fast).

✍️ Practice

Create a single-field index on a field you often filter by, then add a compound index on two fields.
Run a find() with .explain("executionStats") before and after adding the index and compare the stage and totalDocsExamined.

🏠 Homework

Add a unique index on a users collection’s email field, then try to insert two users with the same email and read the error you get.