Index intersection behavior in MongoDB - Step-by-Step Execution

Concept Flow - Index intersection behavior

Query with multiple conditions

↓

Check available indexes

↓

Find indexes matching each condition

↓

Intersect index results

↓

Fetch documents matching all conditions

↓

Return results

When a query has multiple conditions, MongoDB finds indexes for each condition, intersects their results, and returns documents matching all conditions.

Execution Sample

MongoDB

db.collection.find({age: {$gt: 25}, city: 'NY'})

Query finds documents where age is greater than 25 and city is 'NY' using index intersection.

Execution Table

Step	Action	Indexes Used	Intermediate Result	Explanation
1	Receive query with conditions age > 25 and city = 'NY'	None yet	N/A	Start with query filters
2	Check indexes on 'age' and 'city'	Index on age, Index on city	N/A	Identify indexes that can help
3	Use index on age to find docs with age > 25	Index on age	Docs matching age > 25	Get doc IDs matching age condition
4	Use index on city to find docs with city = 'NY'	Index on city	Docs matching city = 'NY'	Get doc IDs matching city condition
5	Intersect doc IDs from both indexes	Index on age, Index on city	Docs matching both conditions	Find docs present in both sets
6	Fetch full documents for intersected IDs	N/A	Final result set	Return documents matching all conditions
7	Return results to client	N/A	Query complete	Query execution finished

💡 All conditions processed and intersected, final matching documents returned

Variable Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	Final
Docs_age	N/A	IDs with age > 25	IDs with age > 25	IDs intersected	Final matched docs
Docs_city	N/A	N/A	IDs with city = 'NY'	IDs intersected	Final matched docs

Key Moments - 3 Insights

Why does MongoDB use index intersection instead of a single combined index?

MongoDB uses index intersection when no single index covers all query conditions, so it combines multiple indexes to efficiently find matching documents (see execution_table steps 2-5).

Does index intersection return documents that match only one condition?

No, index intersection returns documents that match all conditions by intersecting the sets of document IDs from each index (see execution_table step 5).

What happens if one condition has no supporting index?

MongoDB cannot use index intersection fully and may scan documents or use a single index for the other condition, reducing efficiency (not shown in this trace).

Visual Quiz - 3 Questions

Look at the execution_table, at which step does MongoDB find documents matching the city condition?

A Step 3

B Step 4

C Step 5

D Step 6

💡 Hint

Check the 'Action' and 'Indexes Used' columns for city condition filtering

According to variable_tracker, what does 'Docs_age' contain after Step 3?

A All documents in the collection

B IDs of documents with city = 'NY'

C IDs of documents with age > 25

D Final matched documents

💡 Hint

Look at the 'After Step 3' column for 'Docs_age' variable

If there was no index on 'city', how would the execution_table change?

A Step 4 would be skipped or use collection scan

B Step 3 would be skipped

C Intersection would happen earlier

D Final results would be empty

💡 Hint

Consider what happens when an index is missing for a query condition

Concept Snapshot

Index Intersection Behavior in MongoDB:
- When a query has multiple conditions, MongoDB finds indexes for each.
- It retrieves document IDs matching each condition separately.
- Then it intersects these ID sets to find documents matching all conditions.
- This improves query efficiency when no single index covers all conditions.
- Final documents are fetched and returned after intersection.

Full Transcript

This visual execution trace shows how MongoDB handles index intersection for a query with multiple conditions. First, MongoDB receives a query filtering documents by age greater than 25 and city equal to 'NY'. It checks available indexes on these fields. Then it uses the index on age to find documents matching the age condition and separately uses the index on city to find documents matching the city condition. MongoDB intersects the document IDs from both indexes to find documents matching both conditions. Finally, it fetches the full documents for these intersected IDs and returns them as the query result. The variable tracker shows how the sets of document IDs evolve after each step. Key moments clarify why intersection is used and how it ensures all conditions are met. The quiz questions test understanding of the steps and variable states in the execution.