How indexes cut my MongoDB query from 100,001 documents to 100
What I'm building
An Audit-Log service that helps to identify who changed what and when, Stack: Node js, Express, mongodb
The setup
I seeded 100,000 fake events (actors, actions, resources, timestamps spread over 90 days). The query my API runs most:
"Give me the 100 newest events by user_1 between two dates."
The collection had one extra test event I created through the API, so scans show 100, 001
Before: no index
Using Compass's Explain Plan (which runs explain("executionStats") ):
Docs examined Returned In-memory sort Stage
100,001 100 yes COLLSCAN
MongoDB opened every document to return 100.
After: one compound index
Index: { actor: 1, timestamp: -1 }
Docs examined Returned In-memory sort Stage
100 100 no IXSCAN
Why field order matters (ESR: Equality, Sort, Range)
In a compound index, order fields like this:
Equality : fields matched with an exact value
Sort : the field the results are ordered by
Range : fields filtered…