Indexing MongoDB for read-heavy work
An index is a sorted copy of one or more fields, and a query is fast exactly when it can walk that copy instead of the documents.
What an index actually is
An index stores the values of the indexed fields in sorted order, each paired with a pointer to the document it came from. A query that filters on those fields can jump straight to the matching range instead of reading every document and testing it.
That structure has to be maintained. Every write updates every index the written fields appear in, so an index is a trade: reads get cheaper, writes get more expensive, and disk use grows.
Compound order matters
A compound index sorts by the first field, then by the second within each first value, and so on. A query can use a leading portion of that order and nothing else. An index on user then created can serve a filter on user alone, and a filter on user with a sort by created, but it cannot serve a filter on created alone.
Putting the equality fields first, then the range field, then the sort field is the ordering that serves the widest set of queries from one index.
Reading the plan
The query planner will explain what it chose. The thing to look for is whether it scanned the collection or an index, and how many documents it examined compared to how many it returned. Those two numbers being far apart is the signal that an index is missing or is not the one the query needs.
Adding an index deliberately
- Collect the queries that actually run, not the ones the schema suggests.
- Group them by the fields they filter and sort on.
- For each group, write one compound index with equality fields first, then range, then sort.
- Ask the planner to explain each query and compare documents examined against documents returned.
- Remove indexes no query plan chose; each one is paid for on every write.
// serves: find({user, created: {$gt: t}}).sort({created: -1})
db.events.createIndex({ user: 1, created: -1 })Equality first, range next, sort last