Pebellslearn by building
0%
RoadmapsMatrixCoursesSandboxSign in
Creating Indexes  ›  Lesson

Single Field Indexes

Single Field 12 minAn index is a sorted list of one field's values pointing back to documents, so queries on that field examine only matching entries instead of scanning the whole collection, at the cost of extra work on every write.

Single Field Indexes

With five books, any query is instant. With two million, find({ author: "Jane Austen" }) has to open every single document to check its author. An index is the book-shop equivalent of an alphabetical author shelf: go straight to "A", read along, stop.

The rule

Index the field you filter on; turn COLLSCAN into IXSCAN.

db.books.createIndex({ author: 1 })        // 1 ascending, -1 descending
db.books.getIndexes()                      // _id_ and author_1
db.books.find({ author: "Jane Austen" }).explain("executionStats")
  • An index stores the field's values in sorted order, each pointing to its document.
  • Every collection has an index on _id already, created automatically and not removable.
  • For a single field, direction (1 or -1) does not matter: MongoDB can walk the index either way, so it serves sort({ author: 1 }) and sort({ author: -1 }) alike.
  • An index on an array field (a multikey index) gets one entry per element, so { genres: 1 } helps { genres: "classic" }.
  • { unique: true } makes the index reject duplicates: createIndex({ isbn: 1 }, { unique: true }).
  • Every index costs: inserts, updates and deletes must also update it, and it uses disk and memory. Index what you query, not everything.

The shortcut

Read explain("executionStats") for three numbers: stage, docs examined, docs returned. Good queries examine about as many documents as they return.

Searching two million books by authorStep 1 of 4

  1. Before the index

    Stage COLLSCAN. totalDocsExamined 2,000,000. nReturned 7. Almost all the work was wasted.

The trap: creating an index and assuming it is used. Only explain tells you which plan actually won.

Drills

Answer fast — trust the rule.

quick drill

Which field is indexed in every collection automatically?

quick drill

In explain output, which stage means every document was scanned?

quick drill

A query examined 900,000 documents and returned 12. What does that suggest?

quick drill

Why not index every field "just in case"?

Remember

  • Index the field you filter on; turn COLLSCAN into IXSCAN.
  • Shortcut: in explain("executionStats"), compare docs examined with docs returned.
  • Trap: indexes cost writes and memory, and only explain proves one is used.

Practice — level up

0/1 solved

Write your answer, then compare it with a rubric and a model answer.

ExplainProving an index helps

The books collection has grown to two million documents and searches by author are slow. Explain what you would run to diagnose it, what index you would create, and what you would expect to see in the explain output before and after.