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.

Practice

1. What is the main purpose of a partial index in MongoDB?

easy

A. To index only documents that match a specific filter condition

B. To index all documents in a collection

C. To create a backup of the index

D. To speed up writes by disabling indexing

Solution

1. Step 1: Understand what partial indexes do

   Partial indexes only include documents that meet a filter condition, unlike normal indexes that include all documents.

2. Step 2: Compare options

   To index only documents that match a specific filter condition correctly describes this behavior. Options A, B, and D describe other unrelated or incorrect behaviors.

3. Final Answer:

   To index only documents that match a specific filter condition -> Option A

4. Quick Check:

   Partial index = filtered indexing [OK]

Hint: Partial indexes filter documents before indexing [OK]

Common Mistakes:

• Thinking partial indexes index all documents
• Confusing partial indexes with backups
• Assuming partial indexes disable indexing

2. Which of the following is the correct syntax to create a partial index on the status field where status equals 'active'?

easy

A. db.collection.createIndex({status: 1}, {filter: {status: 'active'}})

B. db.collection.createIndex({status: 1}, {partialFilterExpression: {status: 'active'}})

C. db.collection.createPartialIndex({status: 1}, {status: 'active'})

D. db.collection.createIndex({status: 1}, {partialIndex: {status: 'active'}})

Solution

1. Step 1: Recall the correct option name for partial indexes

   The option to specify a filter for partial indexes is partialFilterExpression.

2. Step 2: Match syntax with options

   db.collection.createIndex({status: 1}, {partialFilterExpression: {status: 'active'}}) uses partialFilterExpression correctly. Options B, C, and D use incorrect option names or methods.

3. Final Answer:

   db.collection.createIndex({status: 1}, {partialFilterExpression: {status: 'active'}}) -> Option B

4. Quick Check:

   Use partialFilterExpression for partial indexes [OK]

Hint: Use partialFilterExpression option to filter index documents [OK]

Common Mistakes:

• Using 'filter' instead of partialFilterExpression
• Trying to use createPartialIndex method (doesn't exist)
• Using partialIndex option instead of partialFilterExpression

3. Given the following partial index creation:

db.users.createIndex({age: 1}, {partialFilterExpression: {age: {$gte: 18}}})

Which documents will be included in this index?

medium

A. All documents regardless of age

B. Only documents where age is less than 18

C. Only documents where age is 18 or older

D. Documents where age is exactly 18

Solution

1. Step 1: Understand the filter condition

   The filter {age: {$gte: 18}} means age greater than or equal to 18.

2. Step 2: Determine which documents match

   Only documents with age 18 or older satisfy this condition and will be indexed.

3. Final Answer:

   Only documents where age is 18 or older -> Option C

4. Quick Check:

   Filter {$gte: 18} means age ≥ 18 [OK]

Hint: Partial index includes only documents matching filter [OK]

Common Mistakes:

• Thinking all documents are indexed
• Confusing $gte with $lt
• Assuming only exact matches are indexed

4. You tried to create a partial index with this command:

db.orders.createIndex({price: 1}, {partialFilterExpression: {price: {$gt: 100}}})

But you get an error. What is the most likely cause?

medium

A. The filter expression uses an unsupported operator

B. The field name in the index is missing

C. Partial indexes cannot use comparison operators

D. The partialFilterExpression option is misspelled or misplaced

Solution

1. Step 1: Check syntax correctness

   The syntax shown is correct if partialFilterExpression is spelled and placed properly.

2. Step 2: Identify common mistakes

   Errors often happen if partialFilterExpression is misspelled or placed outside the options object.

3. Final Answer:

   The partialFilterExpression option is misspelled or misplaced -> Option D

4. Quick Check:

   Check spelling and placement of partialFilterExpression [OK]

Hint: Check spelling and position of partialFilterExpression [OK]

Common Mistakes:

• Misspelling partialFilterExpression
• Placing filter outside options object
• Assuming comparison operators are unsupported

5. You want to speed up queries on a collection products where only documents with stock greater than zero are frequently queried. Which partial index creation command is best?

hard

A. db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: {$gt: 0}}})

B. db.products.createIndex({stock: 1})

C. db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: 0}})

D. db.products.createIndex({stock: 1}, {filter: {stock: {$gt: 0}}})

Solution

1. Step 1: Identify the filter condition for partial index

   The goal is to index only documents where stock is greater than zero.

2. Step 2: Match the correct syntax and filter

   db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: {$gt: 0}}}) uses partialFilterExpression with {stock: {$gt: 0}}, which matches the requirement.

3. Step 3: Evaluate other options

   db.products.createIndex({stock: 1}) indexes all documents (not partial). db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: 0}}) filters only stock equal to zero, which is wrong. db.products.createIndex({stock: 1}, {filter: {stock: {$gt: 0}}}) uses incorrect option name filter.

4. Final Answer:

   db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: {$gt: 0}}}) -> Option A

5. Quick Check:

   Use partialFilterExpression with correct filter [OK]

Hint: Use partialFilterExpression with correct filter condition [OK]

Common Mistakes:

• Indexing all documents instead of partial
• Using wrong filter values
• Using incorrect option names like 'filter'