Indexing

What an Index Actually Is

Think of the index at the back of a textbook. Without it, finding every mention of "photosynthesis" means turning every page. With it, you look up one word and jump straight to the right pages. A database index is the same idea: a separate, sorted structure holding the values of one or more fields together with pointers to the documents that contain them.

Without a suitable index, MongoDB performs a collection scan — it reads every single document and tests each one against your filter. On the fifty documents in your practice database this is instant, which is exactly why indexing feels like a topic you can postpone. On a collection with two lakh orders it is the difference between a page that loads immediately and a page that times out. The query is identical; only the amount of work behind it changed.

Indexes are stored and maintained by the database itself. You do not query them directly and you never write code that reads one. You create them once, and the query planner decides on its own whether using an index is cheaper than scanning.

// Create an index on a single field: 1 = ascending, -1 = descending
db.users.createIndex({ email: 1 })
// email_1     <- the generated index name

db.orders.createIndex({ placedAt: -1 })

// See what exists
db.users.getIndexes()

// Remove one by name
db.users.dropIndex("email_1")

Proving It Works with explain()

Guessing whether an index is being used is a waste of an afternoon. explain("executionStats") tells you exactly what happened, and three numbers in its output answer almost every question you will have.

nReturned is how many documents the query gave back. totalDocsExamined is how many it had to look at to find them. totalKeysExamined is how many index entries it read. The health of a query is the relationship between these: if you return 10 documents after examining 200,000, you are scanning. If you return 10 after examining 10, the index took you straight there.

The other thing to read is the winning plan's stage. COLLSCAN means a full collection scan and is the word to look for when something is slow. IXSCAN means an index was used. IXSCAN followed by FETCH means the index found the entries and then MongoDB went to the documents to read the remaining fields — which is normal. If the plan shows only IXSCAN with no FETCH, every field the query needed was inside the index itself. That is called a covered query, and it is the fastest thing MongoDB does.

// Before any index
db.orders.find({ customerId: 42 }).explain("executionStats")
// winningPlan.stage : "COLLSCAN"
// nReturned         : 3
// totalDocsExamined : 200000     <- read everything to find 3

db.orders.createIndex({ customerId: 1 })

// After
db.orders.find({ customerId: 42 }).explain("executionStats")
// winningPlan.stage : "FETCH" -> inputStage.stage: "IXSCAN"
// nReturned         : 3
// totalKeysExamined : 3
// totalDocsExamined : 3

// A covered query: everything needed lives in the index
db.orders.createIndex({ customerId: 1, status: 1 })
db.orders.find({ customerId: 42 }, { status: 1, _id: 0 }).explain("executionStats")
// totalDocsExamined : 0

Compound Indexes: Field Order Is Everything

A compound index covers several fields in a fixed order, and that order decides which queries it can serve. Picture a phone directory sorted by city and then by surname. Finding everyone in Pune is easy. Finding everyone in Pune whose surname is Sharma is easy. Finding every Sharma in the country is not — the book is not sorted that way, and you would have to read all of it.

MongoDB follows the same rule, called the prefix rule. An index on { category: 1, price: -1, brand: 1 } can serve queries on category, on category + price, and on all three. It cannot efficiently serve a query on price alone or on brand alone. This is why one well-ordered compound index often replaces three single-field indexes — and why the wrong order helps nobody.

For choosing the order, MongoDB's own guidance is the ESR rule: fields tested for Equality first, then the field you Sort by, then fields used in a Range. A query filtering on category exactly, sorting by price, and filtering rating above 4 wants the index { category: 1, price: -1, rating: 1 }. Put the range field before the sort field and MongoDB can still use the index for filtering, but it has to sort the results in memory afterwards.

db.products.createIndex({ category: 1, price: -1 })

// Served well by that index
db.products.find({ category: "Electronics" })
db.products.find({ category: "Electronics" }).sort({ price: -1 })
db.products.find({ category: "Electronics", price: { $lt: 20000 } })

// NOT served — price is not a prefix of the index
db.products.find({ price: { $lt: 20000 } })          // collection scan

// ESR: equality (category), sort (price), range (rating)
db.products.createIndex({ category: 1, price: -1, rating: 1 })
db.products.find({ category: "Electronics", rating: { $gte: 4 } })
  .sort({ price: -1 })

Index Types Worth Knowing

Beyond plain single and compound indexes, a few variants solve specific problems. A unique index refuses a second document with the same value — this is how you enforce "one account per email address", and it is the only reliable way to do so, because a check in your application code can always lose a race with a simultaneous signup.

A partial index only indexes documents matching a condition, which keeps it small when you only ever query a subset. A TTL index deletes documents once a date field passes an age you set. A text index enables word-based search with $text; a collection may have only one, though it can span several fields. A multikey index is what you automatically get when you index an array field — MongoDB stores one index entry per element, which is what makes { tags: "sale" } fast.

One warning about unique indexes: creating one on a collection that already contains duplicates fails, and the error names the clashing value. Find and fix the duplicates first, usually with an aggregation that groups by the field and keeps only groups with a count above one.

// Uniqueness enforced by the database, not by your code
db.users.createIndex({ email: 1 }, { unique: true })

// Only index the rows you actually query
db.orders.createIndex(
  { placedAt: -1 },
  { partialFilterExpression: { status: "pending" } }
)

// Self-cleaning data
db.otps.createIndex({ createdAt: 1 }, { expireAfterSeconds: 300 })

// Word search across two fields
db.articles.createIndex({ title: "text", body: "text" })

// Find duplicates before adding a unique index
db.users.aggregate([
  { $group: { _id: "$email", n: { $sum: 1 } } },
  { $match: { n: { $gt: 1 } } }
])

Indexes Are Not Free

Every index you create must be kept up to date. An insert writes the document and then adds an entry to every index on that collection; an update to an indexed field rewrites index entries too. A collection with eight indexes does roughly eight times the index maintenance work per write of a collection with one. Indexes also occupy disk, and — more importantly — compete for memory, because MongoDB is fastest when the indexes it uses fit in RAM.

This is why "index everything" is bad advice, and why an unused index is worse than no index: you pay for it on every write and gain nothing. Before adding one, know which query it is for. Every few months, check which indexes are actually being used and drop the ones that are not.

• Index the fields you filter on, sort by, and join on with $lookup
• Prefer one well-ordered compound index over several overlapping single-field ones
• Do not index a field that has only two or three distinct values on its own — an index on a true/false flag rules out almost nothing, though the same field can be useful as part of a compound index
• Do not create an index for a query that runs once a month over a small collection
• Measure with explain("executionStats") before and after, rather than trusting intuition
• Remember that _id is indexed already — you never need to create it