Compound index and field order in MongoDB - Step-by-Step Execution

Concept Flow - Compound index and field order

Create compound index with fields

↓

MongoDB stores index entries

↓

Query uses index fields in order

↓

Efficient query if prefix fields match

↓

Query uses index to speed up search

↓

If order mismatched, index not used fully

This flow shows how MongoDB creates a compound index with fields in a specific order and how queries use that order to efficiently find data.

Execution Sample

MongoDB

db.collection.createIndex({ age: 1, name: 1 })
db.collection.find({ age: 25, name: "Alice" })

Create a compound index on age and name, then query documents matching both fields.

Execution Table

Step	Action	Index State	Query Condition	Index Usage
1	Create compound index on {age:1, name:1}	Index entries sorted by age then name	None	Index ready
2	Query with {age:25, name:'Alice'}	Index unchanged	age=25 and name='Alice'	Full index used for search
3	Query with {age:25}	Index unchanged	age=25	Index used on age prefix only
4	Query with {name:'Alice'}	Index unchanged	name='Alice'	Index NOT used efficiently
5	Query with {name:'Alice', age:25}	Index unchanged	name='Alice' and age=25	Index NOT used efficiently
6	End of demonstration	Index unchanged	No more queries	Execution stops

💡 Queries stop after demonstrating index usage with different field orders

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
Index	None	Compound index on {age:1, name:1}	Same	Same	Same	Same
Query Condition	None	{age:25, name:'Alice'}	{age:25}	{name:'Alice'}	{name:'Alice', age:25}	None
Index Usage	None	Full index used	Partial index used	Not used efficiently	Not used efficiently	None

Key Moments - 2 Insights

Why does the query {name:'Alice'} not use the compound index efficiently?

Because the compound index is ordered with age first, MongoDB can only use the index efficiently if the query includes the first field (age). Querying only by name skips the first field, so the index is not used fully (see execution_table row 4).

What happens if the query fields are reversed like {name:'Alice', age:25}?

The query does not use the index efficiently. The order of fields in the query document does not affect index usage; MongoDB uses the index prefix order (age then name). Since the query does not specify the first field alone or as a prefix, the index cannot be used fully (see execution_table row 5).

Visual Quiz - 3 Questions

Look at the execution table, at which step does the query use the full compound index?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Check the 'Index Usage' column for 'Full index used'

According to the variable tracker, what is the index usage after querying {age:25}?

A Full index used

B Index not used

C Partial index used

D Index rebuilt

💡 Hint

Look at 'Index Usage' after Step 3 in variable_tracker

If the compound index was created as {name:1, age:1}, which query would use the index efficiently?

A {age:25, name:'Alice'}

B {name:'Alice'}

C {age:25}

D {name:'Alice', age:25}

💡 Hint

Index usage depends on the first field in the index order

Concept Snapshot

Compound index syntax: createIndex({field1: order, field2: order})
Order matters: MongoDB uses index efficiently only if query matches prefix fields in order
Query with first field missing cannot use index fully
Queries matching prefix fields use index to speed up search
Field order affects which queries benefit from the index

Full Transcript

This visual execution shows how MongoDB compound indexes work with field order. First, a compound index is created on age and name. The index stores entries sorted by age first, then name. Queries that include age first can use the index fully, speeding up search. Queries that only specify name do not use the index efficiently. The execution table traces queries with different conditions and shows how index usage changes. The variable tracker records the index state, query conditions, and index usage after each step. Key moments clarify why field order matters and how it affects query performance. The visual quiz tests understanding of index usage at different steps and how changing field order affects queries. The snapshot summarizes the key rules for compound indexes and field order in MongoDB.