C100DEV Indexing Practice Question
A team stores user profiles in a `profiles` collection. About 90% of documents have a `deletedAt` field set to null, and only 10% have an actual timestamp indicating soft deletion. Queries that list active users filter on `deletedAt: null` and sort by `createdAt`. The team wants an index that stays small and avoids maintaining entries for deleted documents. Which index definition best matches this requirement?
⚠ Common exam trap
The trap here is reaching for a sparse index to exclude documents, when sparse only skips missing fields and ignores documents whose field is present but null.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
db.profiles.createIndex({ createdAt: 1 }, { partialFilterExpression: { deletedAt: null } })
A partial index limits entries to documents matching its filter expression. Filtering on `deletedAt: null` stores only active profiles, so the index is far smaller than a full compound or sparse index and still supports the equality-plus-sort query. Sparse indexes are unsuitable because the field exists with a null value rather than being absent.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
db.profiles.createIndex({ createdAt: 1 }, { partialFilterExpression: { deletedAt: null } })
Why this is correct
A partial index with `partialFilterExpression: { deletedAt: null }` only stores entries for active documents, roughly 10% of the collection. It supports the equality filter on `deletedAt: null` and the sort on `createdAt`, while dramatically reducing index size and write overhead compared to indexing every document.
- ✗
db.profiles.createIndex({ createdAt: 1 }, { expireAfterSeconds: 0 })
Why it's wrong here
TTL indexes delete documents after a date field elapses; they do not filter which documents receive index entries. Using `expireAfterSeconds` here would schedule deletion of documents based on `createdAt`, which is not the intended soft-delete behavior and would not produce a smaller index for active users.
- ✗
db.profiles.createIndex({ deletedAt: 1 }, { sparse: true })
Why it's wrong here
A sparse index omits documents where the indexed field is missing, but `deletedAt` exists with value null in the 90% of active documents. Sparse indexes do not skip null values, so this index would still contain an entry for nearly every document and would not support the sort on `createdAt`.
- ✗
db.profiles.createIndex({ deletedAt: 1, createdAt: 1 })
Why it's wrong here
This is a standard compound index that indexes every document, including the 90% with `deletedAt: null`. It supports the equality-then-sort pattern but does not reduce index size, since all documents receive entries. It works functionally but fails the stated goal of keeping the index small by excluding deleted records.
About these practice questions
Courseiva writes every C100DEV question from scratch — 259 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official MongoDB exam blueprint
This C100DEV practice question is part of Courseiva's free MongoDB certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the C100DEV exam.