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

The_CrackedAhki — Tue, 10 Nov 2026 06:22:34 +0000

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 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

Query	Index used	Examined (no index)	Examined (indexed)	Returned	Stage (indexed)
`actor`	`actor_1_timestamp_-1` (prefix)	100,001	27,188	27,188	IXSCAN
`action`	`action_1_timestamp_-1` (prefix)	100,001	33,124	33,124	IXSCAN
`timestamp` range	`timestamp_-1`	100,001	17,765	17,765	IXSCAN
`actor` + range	`actor_1_timestamp_-1`	100,001	4,772	4,772	IXSCAN
`actor` + range + sort + limit 100	`actor_1_timestamp_-1`	100,001	100	100	IXSCAN

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

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

The new beginning: grinding like my life depends on it

The_CrackedAhki — Tue, 25 Aug 2026 11:58:31 +0000

This is my first post on this platform and I intend on using this platform to share my progress and everything I learnt along the way, the ups the downs the failure and the little wins.

DEV Community: The_CrackedAhki