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.

Practice

1. What does a compound index in MongoDB do?

easy

A. Speeds up queries that filter or sort on multiple fields together

B. Stores data in multiple collections

C. Automatically backs up the database

D. Encrypts data for security

Solution

1. Step 1: Understand what an index is

   An index helps MongoDB find data faster by organizing it.

2. Step 2: Understand compound index purpose

   A compound index uses more than one field to speed up queries that use those fields together.

3. Final Answer:

   Speeds up queries that filter or sort on multiple fields together -> Option A

4. Quick Check:

   Compound index = Speeds up multi-field queries [OK]

Hint: Compound index = multiple fields speed up queries [OK]

Common Mistakes:

• Thinking it stores data in multiple collections
• Confusing with backup or encryption features
• Believing it only indexes one field

2. Which is the correct way to create a compound index on fields age ascending and score descending in MongoDB?

easy

A. db.collection.createIndex({ age: 1, score: -1 })

B. db.collection.createIndex({ age: -1, score: 1 })

C. db.collection.createIndex(["age", "score"])

D. db.collection.createIndex({ age: 'asc', score: 'desc' })

Solution

1. Step 1: Recall syntax for createIndex

   MongoDB uses an object with field names and 1 or -1 for ascending/descending.

2. Step 2: Match field order and directions

   Age ascending is 1, score descending is -1, so { age: 1, score: -1 } is correct.

3. Final Answer:

   db.collection.createIndex({ age: 1, score: -1 }) -> Option A

4. Quick Check:

   Use 1/-1 for asc/desc in object [OK]

Hint: Use {field: 1/-1} object for compound index [OK]

Common Mistakes:

• Using array instead of object for fields
• Using strings like 'asc' or 'desc' instead of 1/-1
• Mixing ascending and descending incorrectly

3. Given a compound index on { city: 1, rating: -1 }, which query will use the index efficiently?

medium

A. db.collection.find({ rating: { $gt: 4 } })

B. db.collection.find({ city: 'NYC' }).sort({ rating: 1 })

C. db.collection.find({ city: 'NYC' }).sort({ rating: -1 })

D. db.collection.find({ rating: -1, city: 'NYC' })

Solution

1. Step 1: Understand index field order and directions

   The index is on city ascending, then rating descending.

2. Step 2: Check query filter and sort usage

   Query B filters on city (first field) and sorts rating descending (matching index direction).

3. Final Answer:

   db.collection.find({ city: 'NYC' }).sort({ rating: -1 }) -> Option C

4. Quick Check:

   Filter first field, sort second field matching index [OK]

Hint: Filter first field, sort second field matching index order [OK]

Common Mistakes:

• Sorting rating ascending when index is descending
• Filtering only on rating without city
• Ignoring field order in index

4. You created a compound index { a: 1, b: 1 } but your query { b: 5 } is slow. What is the likely issue?

medium

A. MongoDB does not support compound indexes

B. The query does not filter on the first indexed field a

C. The index fields are in wrong order; should be { b: 1, a: 1 }

D. The index must use -1 for descending order

Solution

1. Step 1: Recall how compound indexes work

   Compound indexes work best when queries filter on the first field in the index.

2. Step 2: Analyze query filter

   The query filters only on b, the second field, so index on a then b is not used efficiently.

3. Final Answer:

   The query does not filter on the first indexed field a -> Option B

4. Quick Check:

   Filter must include first index field [OK]

Hint: Query must filter first indexed field to use compound index [OK]

Common Mistakes:

• Thinking MongoDB doesn't support compound indexes
• Believing field order doesn't matter
• Assuming descending order is always required

5. You want to optimize queries filtering by category and sorting by price ascending. Which compound index is best?

hard

A. db.collection.createIndex({ price: -1, category: 1 })

B. db.collection.createIndex({ price: 1, category: 1 })

C. db.collection.createIndex({ category: -1, price: -1 })

D. db.collection.createIndex({ category: 1, price: 1 })

Solution

1. Step 1: Identify query pattern

   Queries filter by category and sort by price ascending.

2. Step 2: Choose index field order and direction

   Index should have category first (filter), then price ascending (sort) to match query.

3. Final Answer:

   db.collection.createIndex({ category: 1, price: 1 }) -> Option D

4. Quick Check:

   Filter field first, sort field second with matching order [OK]

Hint: Filter field first, sort field second with matching order [OK]

Common Mistakes:

• Putting sort field before filter field in index
• Using descending order when query sorts ascending
• Ignoring field order importance