Compound Indexes in MongoDB Explained

Compound Indexes in MongoDB Explained

Single-field indexes cover single-field queries. Compound indexes cover multi-field queries. Here is how they work in MongoDB.

What Is a Compound Index

An index on two or more fields. MongoDB can use it for queries that filter on any prefix of the indexed fields.

connectionRequestSchema.index({ fromUserId: 1, toUserId: 1 });

This indexes (fromUserId, toUserId). MongoDB can use it for:

• Queries on fromUserId alone.
• Queries on fromUserId AND toUserId.

It cannot use it efficiently for:

• Queries on toUserId alone (toUserId is not a prefix).

Index Prefix Rule

A compound index on (A, B, C) can be used for:

• A
• A + B
• A + B + C

It cannot be used for:

• B
• C
• B + C

If you query on B + C, you need a separate index on (B, C) or a different compound index that starts with B.

Order Matters

The order of fields in the index matters. Put the field you filter by first, then the field you sort by.

messageSchema.index({ connectionId: 1, createdAt: -1 });

This is good for queries that filter by connectionId and sort by createdAt descending (e.g., loading the latest messages in a chat).

Unique Compound Indexes

connectionRequestSchema.index({ fromUserId: 1, toUserId: 1 }, { unique: true });

Prevents duplicate requests from A to B. Also speeds up the query "did A already swipe on B?"

ESR (Equality, Sort, Range) Rule

A good rule for ordering compound index fields:

1. Equality: fields with exact match (fromUserId = X).
2. Sort: fields used for sorting (createdAt).
3. Range: fields with range queries (age >= 18).

For a query like { fromUserId: X, age: { $gte: 18 } }.sort({ createdAt: -1 }), the index would be { fromUserId: 1, createdAt: -1, age: 1 }.

How to Verify an Index Is Used

ConnectionRequest.find({ fromUserId, toUserId }).explain('executionStats');

Look at executionStats.totalDocsExamined. If it equals the collection size, the index is not used. If it is small, the index is working.

Indexes Have a Cost

Every index adds overhead to writes. Do not index every field. Index the queries you actually run. Use explain() to find slow queries and index for them.

Covered Queries

If the index covers all the fields you need, MongoDB can return results from the index without fetching the document. Faster.

User.find({ email }).select('email').lean();

With an index on email, this is a covered query. MongoDB reads only the index.

The Takeaway

Compound indexes cover multi-field queries. The index prefix rule: MongoDB can use (A, B, C) for A, A+B, or A+B+C, but not for B, C, or B+C. Order matters: equality, sort, range. Use unique compound indexes for uniqueness. Verify with explain(). Indexes have a write cost; index the queries you actually run.