Performance and explain

In plain English

An index is a sorted structure MongoDB can walk to find matching documents without scanning every one. A compound index stores several fields in one order, and that order matters because the index is read from left to right. explain("executionStats") runs a query and reports how many documents were examined and which index, if any, was chosen. A covered query is one where the index alone can satisfy the request, so MongoDB never needs to read the full documents. Adding an index speeds reads but slows writes, because each new index must be maintained.

Step by step

1. 1Run a query and call explain("executionStats") to see the plan.
2. 2Read winningPlan to see which index stage was used.
3. 3Compare totalDocsExamined with the number of documents returned.
4. 4Create a compound index whose first field matches the equality filter.
5. 5Add a projection so the query can be answered by the index alone.

db.lessons.createIndex({ published: 1, minutes: -1 })

db.lessons.find(
  { published: true }
).sort({ minutes: -1 }).explain("executionStats")

db.lessons.createIndex({ slug: 1, title: 1 })

db.lessons.find(
  { slug: "queries" },
  { _id: 0, title: 1 }
).explain("executionStats")

Common mistakes

• Ordering a compound index with the sort field first makes it useless for an equality filter that comes before the sort.
• Assuming a query is fast without reading explain leaves a slow collection scan undiscovered.
• Creating an index on every field wastes storage and slows every write.
• Expecting a covered query to work while selecting a field the index does not contain forces a document fetch.