An audit log service that records who changed what and when. Stack: Node.js, Express, MongoDB.

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")):

MongoDB opened every document to return 100.

After: one compound index

Index: { actor: 1, timestamp: -1 }

Why field order matters (ESR: Equality, Sort, Range)

In a compound index, order fields like this:

1. Equality: fields matched with an exact value
2. Sort: the field the results are ordered by
3. Range: fields filtered with $gte, $lte, and so on

When a field is both the sort and the range, as timestamp is here, it counts as one step and goes last.

Example: my query filters actor = "user_1", limits timestamp to a date range, and sorts by timestamp descending. So the index is { actor: 1, timestamp: -1 }.

• actor goes first, so Mongo jumps straight to that actor's section of the index.
• Inside that section the entries are already ordered by timestamp, so no in-memory sort is needed.
• With limit(100), Mongo reads 100 entries and stops. Result: 100 documents examined, down from 100,001.

The problem I ran into

While testing, I hit a problem with the time-range query. My compound index was { actor: 1, timestamp: -1 }, and a query on timestamp alone couldn’t use it, because an index only helps queries that use its first field. I had two options: change the query to always include actor, or create a separate index on timestamp. I chose the separate index because the audit log needs “everything between these dates” queries across all actors.

Per-query results

What is "prefix": Prefix means that the index can serve for queries that match the first field so for index of actor_1_timestamp_-1 can serve a query of actor because of the first field

What I noticed: for every indexed query, docs examined equals docs returned, so the
index removes the wasted work and only the real work remains. The actor and
time-range rows still examine thousands of documents because they return thousands.
Only the limit brings the work down to 100.

I left execution time out on purpose. The same query ran anywhere from 0 ms to over
200 ms between runs on my local machine, so docs examined is the number I trust. I'll
measure timing properly under load in the next post.

What it costs

Indexes make reads faster and writes slower, because every insert updates each one. For a write-heavy audit log that matters.

Code: https://github.com/BhadmusX/Audit-Log

Next

Cursor pagination, then load testing with k6 or autocannon, then batching and a queue.