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.