MongoDB Indexes Finally Clicked for Me: Understanding Indexes, Compound Indexes & the Prefix Rule 🚀

While working on a MERN project, I came across these indexes:

transactionSchema.index({ user: 1, date: -1 });
transactionSchema.index({ user: 1, type: -1 });
transactionSchema.index({ user: 1, category: -1 });

My first reaction was:

"Why are we creating 3 different indexes for the same schema? Isn't one index enough?"

At that time, my understanding was:

"Indexes help MongoDB find records faster."

Which is true, but it wasn't enough to explain why multiple indexes existed for the same collection.

That simple doubt led me down a rabbit hole of learning about indexes, compound indexes, how MongoDB stores them, and the famous Prefix Rule.

Here's what I learned.

---

What is an Index?

Imagine a collection with millions of transactions.

db.transactions.find({
  user: "Aarthi"
});

Without an index, MongoDB may need to inspect every document until it finds the matching records.

This is called a Collection Scan.

Think of it like searching for a chapter in a book without a table of contents. You'd have to flip through page after page until you find it.

An index works like a book's table of contents.

Instead of scanning every document, MongoDB can jump directly to the relevant records.

Example:

db.transactions.createIndex({
  user: 1
});

Now MongoDB can quickly locate all transactions belonging to a specific user.

---

What is a Compound Index?

A compound index contains multiple fields.

Example:

db.transactions.createIndex({
  user: 1,
  date: -1
});

This means MongoDB organizes the index by:

user
  └── date

Conceptually, it looks something like:

Aarthi
   2025-08-10
   2025-08-09
   2025-08-08

John
   2025-08-10
   2025-08-05

The data is first grouped by user, and within each user, it is ordered by date.

Now queries like:

db.transactions.find({
  user: "Aarthi"
}).sort({
  date: -1
});

become very efficient.

MongoDB can jump directly to Aarthi's records and retrieve them in date order.

---

The Prefix Rule: The Concept That Finally Made It Click

Consider this index:

{
  user: 1,
  date: -1
}

MongoDB can efficiently use it for:

find({
  user: "Aarthi"
});

✅ Works

find({
  user: "Aarthi",
  date: "2025-08-10"
});

✅ Works

But:

find({
  date: "2025-08-10"
});

❌ Not efficient

Why?

Because the index is organized by user first and then by date.

MongoDB knows where each user's records start, but it doesn't know where a specific date begins without first navigating through the user groups.

This behavior is known as the Prefix Rule.

A compound index can efficiently support queries that start from the leftmost fields of the index.

For example:

{
  user: 1,
  date: -1,
  type: 1
}

can efficiently support:

find({ user })

find({ user, date })

find({ user, date, type })

But not:

find({ date })

find({ type })

find({ date, type })

because those queries do not start from the leftmost field.

---

Back to My Original Doubt

I originally saw:

transactionSchema.index({ user: 1, date: -1 });
transactionSchema.index({ user: 1, type: -1 });
transactionSchema.index({ user: 1, category: -1 });

Now it makes sense.

Recent Transactions

find({ user }).sort({ date: -1 });

Uses:

{ user: 1, date: -1 }

---

Filter By Transaction Type

find({
  user,
  type: "expense"
});

Uses:

{ user: 1, type: -1 }

---

Filter By Category

find({
  user,
  category: "food"
});

Uses:

{ user: 1, category: -1 }

Each index is optimized for a different query pattern.

---

Another Question I Had: Where Are Indexes Stored?

Initially, I thought indexes somehow reorganized the actual documents.

But that's not what happens.

MongoDB stores documents and indexes separately.

Conceptually:

Collection
-----------
Doc1
Doc2
Doc3
Doc4

And separately:

Index(user,date)
----------------
Aarthi -> Doc5
Aarthi -> Doc2
Aarthi -> Doc1

Rosy -> Doc8

Index(user,type)
----------------
Aarthi -> expense -> Doc1
Aarthi -> income  -> Doc2

Rosy -> expense -> Doc8

Index(user,category)
--------------------
Aarthi -> food -> Doc1
Aarthi -> travel -> Doc2

Rosy -> food -> Doc8

The actual documents remain unchanged.

Indexes are separate data structures that contain references to documents.

---

Then Why Do We Need .sort() If the Index Is Already Sorted?

This confused me too.

Suppose we have:

{
  user: 1,
  date: -1
}

The index itself is sorted.

However, MongoDB does not guarantee that results should be returned in date order unless we explicitly request it.

For example:

db.transactions.find({
  user: "Aarthi"
});

This may use the index to locate records quickly.

But:

db.transactions.find({
  user: "Aarthi"
}).sort({
  date: -1
});

tells MongoDB:

"Return these records in descending date order."

Since the index is already sorted that way, MongoDB can use the index directly and avoid an expensive in-memory sort.

That's one of the biggest performance benefits of compound indexes.

---

How Does MongoDB Handle Multiple Indexes?

This was another question I had.

Suppose we have:

{ user: 1, date: -1 }

{ user: 1, type: 1 }

{ user: 1, category: 1 }

MongoDB creates three completely separate index structures.

Think of them as three separate books:

Index 1

Aarthi
  2025-08-10
  2025-08-09

Rosy
  2025-08-10

Index 2

Aarthi
  expense
  income

Rosy
  expense

Index 3

Aarthi
  food
  travel

Rosy
  shopping

When a query arrives, MongoDB's query planner decides which index can answer the query most efficiently.

Example:

find({
  user: "Aarthi",
  type: "expense"
});

MongoDB sees:

{ user: 1, type: 1 }

and chooses that index.

For:

find({
  user: "Aarthi"
}).sort({
  date: -1
});

MongoDB chooses:

{ user: 1, date: -1 }

because it perfectly matches the query.

---

Why Not Create One Huge Index?

I also wondered:

{
  user: 1,
  date: -1,
  type: 1,
  category: 1
}

Wouldn't this solve everything?

Not really.

Because of the Prefix Rule.

This index efficiently supports:

find({ user })

find({ user, date })

find({ user, date, type })

But:

find({
  user,
  category
});

is not optimal because date and type appear before category in the index definition.

MongoDB cannot efficiently skip the middle fields.

That's why index design should follow actual query patterns rather than simply including every field.

---

The Trade-Off Most Beginners Miss

Indexes speed up reads.

But they are not free.

Every insert, update, or delete must also update all related indexes.

For example, when inserting:

{
  user: "Aarthi",
  date: "2025-08-10",
  type: "expense",
  category: "food"
}

MongoDB must update:

Index(user,date)

Index(user,type)

Index(user,category)

every single time.

So indexes improve read performance at the cost of:

• Additional storage
• Slower writes
• Extra maintenance

This is the classic database trade-off.

---

My Biggest Takeaway

Before this, I thought:

"Indexes make queries faster."

Now I think:

"Indexes make specific query patterns faster."

Understanding compound indexes, how MongoDB stores them, and the Prefix Rule completely changed the way I think about database design.

The best index is not the one with the most fields.

The best index is the one that matches the queries your application runs most often.

Sometimes a simple question like:

"Why do we have 3 indexes for the same schema?"

can lead to understanding an entire database concept.

If you've had a similar "aha!" moment while learning databases, I'd love to hear it in the comments.

Comments

[aarthirs]

Did you find this post useful? Follow me for more developer-friendly content, share your thoughts in the comments, and help others discover it by sharing this post.