Index from real queries

Without a useful index, MongoDB may scan the collection. Indexes speed supported reads but add storage and write work on every insert and update.

Start from a query your application actually runs, not from a generic index tutorial. Filter and sort together matter: a compound index on { species: 1, age: -1 } helps a query that filters by species and sorts by age, but the field order must match how the query uses them.

Use explain('executionStats') on a representative query. Compare returned documents, examined documents, examined keys, and the execution stages. A COLLSCAN stage means MongoDB read every document in the collection. An IXSCAN stage means it used an index.

Load enough sample documents to make a scan visible. A collection with three rows hides the problem. Insert a few dozen animals with varied species and ages before you compare plans.

Measure the query before and after one index:

db.animals.find({ species: 'cat' }).sort({ age: -1 }).explain('executionStats')
db.animals.createIndex({ species: 1, age: -1 })
db.animals.find({ species: 'cat' }).sort({ age: -1 }).explain('executionStats')

Compare execution stages, totalDocsExamined, totalKeysExamined, and nReturned. Keep the index only when it supports an important query and its write cost is acceptable.

Before the index, note totalDocsExamined and the winning stage. After the index, totalDocsExamined should drop close to nReturned for a selective filter. If the numbers barely move, the index may be wrong, unused, or not selective enough to matter.

Here is what a healthy “after” looks like with 40 animals, 12 of them cats:

// winningPlan.stage: 'FETCH' with inputStage.stage: 'IXSCAN'
// nReturned: 12
// totalKeysExamined: 12
// totalDocsExamined: 12

Twelve keys, twelve documents, twelve results. That is the shape you want. A COLLSCAN with totalDocsExamined: 40 after creating the index means the query does not match the index. The usual culprit is field order: an index on { age: -1, species: 1 } does not help a query that filters on species first.

One more check: db.animals.getIndexes() lists what exists. Every index you see there is paid for on every write, so drop the ones that no query in your application uses.

List the indexes you keep with the query each one serves. Indexes you cannot name are indexes you will forget to maintain.