Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 70 ยท Indexes and performance

How Indexes Work

Understand MongoDB indexes: collection scans versus index scans, the B-tree idea, the automatic _id index, createIndex, and what indexes cost on writes.

Without an index, MongoDB answers a query by reading every document in the collection and checking it against your filter. That is called a collection scan, and it is fine for a few thousand documents and painful for millions. An index is a separate, sorted structure that lets the server jump straight to the documents you want, the way the index at the back of a book beats reading every page.

This lesson explains what an index is, why a sorted structure makes lookups fast, how to create one, and the price you pay on every write. The commands in the shell blocks are real MongoDB. The TryIts run a plain JavaScript simulation of a collection scan and a sorted index over 20,000 generated documents, counting how much work each approach does, so you can see the difference without a server.

Two ways to find a document

Collection scan Read every document, check each.
Index scan Look up the key, jump to the match.

Same answer. Very different amount of work.

1 / 2

What an index stores

A MongoDB index holds, for the indexed field (or fields), the field's values in sorted order, and for each value a pointer to the document that has it. It does not copy the whole document. If you index email, the index contains every email address in alphabetical order, each tied to its document's location on disk.

Sorting is the whole trick. Finding a value in a sorted list does not need a scan: you can halve the search range at each step (binary search), so a million entries need about twenty steps instead of a million. Sorted order also makes range queries (price between 10 and 20) and sorting by that field cheap, because matching entries sit side by side.

MongoDB stores its indexes as B-trees (more exactly B+ tree style structures). A B-tree keeps sorted keys in pages. Upper pages hold signposts that say "keys below 30 are in the left child, 30 to 59 in the middle, 60 and up in the right". The server walks from the root down through a few levels to a leaf page, reads the pointers it finds there, and fetches those documents. Because each page holds many keys, the tree stays only three or four levels deep even for huge collections, and the structure rebalances itself as you add and remove keys.

flowchart TD
    R["Root: 30 | 60"] --> A["Keys below 30<br/>5, 12, 24"]
    R --> B["Keys 30 to 59<br/>31, 42, 55"]
    R --> C["Keys 60 and up<br/>61, 77, 90"]

A tiny B-tree: the root sends a lookup of 42 to the middle page, where the key sits next to its document pointer.

Scan versus index: the numbers

The first stand-in builds 20,000 user documents, then finds one user by email twice: by scanning all documents, and with a sorted array of [email, position] pairs searched by binary search (the same idea as a B-tree's lookup, minus the paging). It counts documents or index entries examined.

JS Collection scan versus index lookup

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Real MongoDB reports the same two numbers in its explain output: totalKeysExamined and totalDocsExamined (explain() and the Profiler). A good index keeps both close to the number of documents returned.

The _id index and createIndex

Every collection gets one index automatically: a unique index on _id. That is why find({ _id: ... }) is always fast, and why you cannot drop it. Everything else you create yourself with createIndex:

JavaScript
db.users.createIndex({ email: 1 })                  // ascending index on email
db.users.createIndex({ email: 1 }, { unique: true }) // also reject duplicates
db.users.createIndex({ country: 1, age: -1 })        // compound: country, then age descending

db.users.getIndexes()                                // list the indexes
db.users.dropIndex("email_1")                        // remove one by name

The 1 or -1 is the sort direction. For a single-field index the direction does not matter much, since MongoDB can walk an index backwards, but it matters for compound indexes (Designing Compound Indexes). Index names default to the key pattern, such as email_1 or country_1_age_-1. Calling createIndex again with the same definition does nothing, so it is safe in setup scripts. Building an index on a large collection takes time and resources, so plan it for a quiet period and check progress before relying on it. Index Types covers the other kinds: compound, multikey (arrays), text, hashed, TTL, partial and more.

Queries use an index when it matches the filter or the sort. The server's query planner picks among the indexes available and the collection scan, often by briefly trying candidate plans and remembering the winner. You do not name the index in your query. If your filter is { email: "..." } and an index starts with email, it will be considered. An index on age is not useful for that query.

Several indexes at once, and removing them

createIndexes (plural) builds several indexes in one operation, which is faster than separate calls on a big collection because the server scans the data once:

JavaScript
db.orders.createIndexes([
  { customerId: 1 },
  { status: 1, createdAt: -1 },
  { notes: "text" }
])

Each entry is a key pattern. To pass options per index, use the createIndexes command, where every index is a document with key, name and its own options:

JavaScript
db.runCommand({
  createIndexes: "orders",
  indexes: [
    { key: { customerId: 1 }, name: "by_customer" },
    { key: { email: 1 }, name: "uniq_email", unique: true }
  ],
  commitQuorum: "majority"      // replica sets (4.4+): how many data-bearing members must finish building
})

commitQuorum matters on replica sets: the build finishes on the primary only after enough members have built the index too ("votingMembers" is the default, a number, "majority" and 0 are alternatives). Other options you will see in createIndex and createIndexes calls:

Option Purpose
name Your own index name instead of the generated field_1
unique, sparse, partialFilterExpression, expireAfterSeconds, hidden See Index Types
collation Language-aware comparison and sorting (for example case-insensitive)
weights, default_language, textIndexVersion Tune text indexes
2dsphereIndexVersion, min, max, bits Geospatial indexes
wildcardProjection Include or exclude fields of a wildcard index ({ "$**": 1 })

To remove indexes, name them or give the key pattern:

JavaScript
db.orders.dropIndex("by_customer")            // one index by name
db.orders.dropIndex({ customerId: 1 })        // or by key pattern
db.orders.dropIndexes(["by_customer", "uniq_email"])   // several by name
db.orders.dropIndexes()                        // ALL indexes except _id
db.orders.hideIndex("by_customer")             // keep it, but make the planner ignore it

The reply of dropIndexes() contains nIndexesWas, the number of indexes before the drop. Dropping is immediate, so prefer hiding first: hide the index, watch performance for a while, and drop it only when nothing got slower. If it did, unhideIndex brings it back instantly, with no rebuild. getIndexes() shows what exists and db.orders.aggregate([{ $indexStats: {} }]) reports how often each index was used.

What indexes cost

Indexes are not free:

  • Write cost. Every insert adds an entry to every index on the collection. Every delete removes entries. An update that changes an indexed field moves the entry. Each extra index adds another structure to update for every write, so ten indexes mean ten extra updates per insert.
  • Space. An index takes disk space, and the active part of it should fit in RAM to stay fast (Common Performance Pitfalls discusses the working set).
  • Planning work. More indexes mean more candidates for the planner, and more things to keep in sync with how your application queries.

The second stand-in counts index maintenance for a batch of inserts as you add more indexes.

JS The write cost of indexes

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The stand-in simplifies things (real B-trees do not shift arrays; they split pages), but the lesson holds: index entries written grows with documents times indexes. A write-heavy collection with many unused indexes pays for all of them on every write and gets nothing back.

When to add an index

Create an index when a query you run often, or that must be fast, examines far more documents than it returns, and when the field has enough variety to be selective (an index on a field with only two values rarely helps much). Drop indexes that no query uses. You will learn how to see index usage and plans in explain() and the Profiler. For a refresher on the same ideas in a relational database, see SQL Indexes.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is a collection scan?

    Without a usable index the server has to read each document and test it, so the work grows with the size of the collection.
  2. 2. Why is searching a sorted index fast?

    Sorted keys allow binary-search style lookups (a few steps even for millions of entries) and make range queries read adjacent entries.
  3. 3. Which index does every collection have automatically?

    MongoDB creates a unique index on _id for every collection (apart from a few special cases) and it cannot be dropped.
  4. 4. A collection has eight indexes and receives many inserts per second. What is the main downside?

    Every index must be maintained on every write. Keep only the indexes your queries need.
  5. 5. You run db.users.createIndex({ email: 1 }) twice with the same definition. What happens the second time?

    Creating an index that already exists with the same definition is a no-op, so setup scripts can safely repeat it.

Summary

  • Without an index, a query is a collection scan: every document is examined.
  • An index is a sorted structure (a B-tree) of field values with pointers to documents, so lookups take a few steps and ranges read adjacent entries.
  • Every collection has a unique _id index; others are made with createIndex({ field: 1 }) and listed with getIndexes().
  • Indexes cost disk space, memory and write speed, because every write maintains each index.
  • Add indexes for frequent or slow queries on selective fields, and drop the ones nobody uses.

Next, meet the different kinds of index in Index Types.