Partial indexes with filter in MongoDB - Step-by-Step Execution

Concept Flow - Partial indexes with filter

Start: Define Partial Index

↓

Specify Filter Condition

↓

Create Index on Filtered Documents

↓

Query Uses Partial Index if Filter Matches

↓

Faster Query on Subset of Data

↓

End

Create an index only on documents matching a filter. Queries using that filter can use the smaller index for faster results.

Execution Sample

MongoDB

db.collection.createIndex(
  { status: 1 },
  { partialFilterExpression: { status: { $eq: "active" } } }
)

Creates an index on the 'status' field only for documents where status is 'active'.

Execution Table

Step	Action	Filter Condition	Index Content	Query Example	Index Used?
1	Create partial index	status == 'active'	Indexes only docs with status='active'	N/A	N/A
2	Insert doc {status:'active', name:'A'}	N/A	Doc included in index	N/A	N/A
3	Insert doc {status:'inactive', name:'B'}	N/A	Doc NOT included in index	N/A	N/A
4	Query {status:'active'}	status=='active'	Index covers matching docs	Uses partial index	Yes
5	Query {status:'inactive'}	status=='inactive'	Index does not cover these docs	Does not use partial index	No
6	Query {name:'A'}	No filter on status	Index not useful	Does not use partial index	No
7	End	N/A	N/A	N/A	N/A

💡 No more steps; partial index only covers documents matching filter condition.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
Index Content	empty	{status:'active', name:'A'}	{status:'active', name:'A'}	used for status='active' queries	not used for status='inactive' queries	partial index covers only active docs

Key Moments - 2 Insights

Why doesn't the partial index include documents where status is 'inactive'?

Because the partialFilterExpression only includes documents where status equals 'active' as shown in execution_table step 1 and step 3 where inactive docs are not indexed.

Can queries without the filter condition use the partial index?

No, queries like {name:'A'} do not match the filter condition, so the partial index is not used (see execution_table step 6).

Visual Quiz - 3 Questions

Look at the execution table, at which step is a document NOT included in the partial index?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Check the 'Index Content' column in step 3 where the inactive document is not included.

At which query does MongoDB use the partial index?

A Query {status:'active'}

B Query {status:'inactive'}

C Query {name:'A'}

D Query {age:30}

💡 Hint

See 'Index Used?' column in execution_table step 4.

If the filter condition changed to status=='inactive', which document would be indexed?

A Document with status 'active'

B Document with status 'inactive'

C Both documents

D No documents

💡 Hint

Partial index only includes documents matching the filter condition, see execution_table step 1.

Concept Snapshot

Partial indexes create an index only on documents matching a filter.
Syntax: createIndex({field:1}, {partialFilterExpression: {field: condition}})
Queries matching the filter use the smaller index for faster results.
Documents not matching filter are not indexed.
Useful to optimize queries on subsets of data.

Full Transcript

Partial indexes in MongoDB let you create an index only on documents that match a filter condition. For example, you can index only documents where status is 'active'. When you create the index, MongoDB scans documents and includes only those matching the filter. Queries that use the filter condition can use this smaller index, making queries faster. Documents that don't match the filter are not included in the index and queries on those documents won't use the partial index. This helps optimize performance when you only query a subset of your data often.