Partial indexes with filter in MongoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Partial Index on Query Performance

Consider a MongoDB collection orders with documents having fields status and total. A partial index is created with filter { status: "completed" } on total. Which query will use this partial index?

MongoDB

db.orders.createIndex({ total: 1 }, { partialFilterExpression: { status: "completed" } })

A db.orders.find({ status: { $in: ["completed", "pending"] }, total: { $gt: 100 } })

B db.orders.find({ status: "pending", total: { $gt: 100 } })

C db.orders.find({ total: { $gt: 100 } })

D db.orders.find({ status: "completed", total: { $gt: 100 } })

💡 Hint

Partial indexes only cover documents matching the filter expression.

The partial index only includes documents where status is "completed". Only queries that filter on status: "completed" can use this index efficiently.

📝 Syntax

intermediate

Correct Syntax for Creating a Partial Index

Which of the following commands correctly creates a partial index on the users collection for documents where age is greater than 18?

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

B db.users.createIndex({ age: 1 }, { filter: { age: { $gt: 18 } } })

C db.users.createIndex({ age: 1 }, { partialFilter: { age: { $gt: 18 } } })

D db.users.createIndex({ age: 1 }, { partialFilterExpression: age > 18 })

💡 Hint

Check the exact option name for partial index filter in MongoDB.

The correct option uses partialFilterExpression with a valid MongoDB query document as the filter.

❓ optimization

advanced

Choosing a Partial Index Filter for Performance

You have a large products collection with a category field. Most queries filter for category: "electronics". Which partial index filter will optimize these queries best?

A { category: { $ne: null } }

B { category: { $exists: true } }

C { category: "electronics" }

D { category: { $in: ["electronics", "appliances"] } }

💡 Hint

Partial indexes should match the most common query filter exactly for best performance.

Filtering the index to only include documents with category: "electronics" makes the index smaller and more efficient for those queries.

🔧 Debug

advanced

Why Does This Partial Index Not Improve Query Speed?

A partial index is created on orders with filter { shipped: true }. The query db.orders.find({ shipped: false, total: { $gt: 50 } }) is slow. Why?

A The query filters for <code>shipped: false</code>, which is not covered by the partial index.

B The partial index filter is invalid and ignored by MongoDB.

C The query does not include the <code>total</code> field in the filter.

D Partial indexes cannot be used with boolean fields.

💡 Hint

Think about which documents the partial index includes.

The partial index only includes documents where shipped is true. Queries filtering for shipped: false cannot use this index and must scan more documents.

🧠 Conceptual

expert

Impact of Partial Indexes on Write Operations

How do partial indexes affect write performance in MongoDB compared to full indexes?

A Partial indexes increase write overhead because the filter must be evaluated for every write.

B Partial indexes reduce write overhead because fewer documents need to be indexed.

C Partial indexes have no effect on write performance compared to full indexes.

D Partial indexes cause write operations to fail if documents do not match the filter.

💡 Hint

Consider how many documents get indexed during writes.

Partial indexes only index documents matching the filter, so fewer index entries are updated during writes, reducing overhead.

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'