Unique, partial, sparse, TTL, text, wildcard, hashed and geo indexes
Every index type and option, what it is for, and the traps in each.
- Enforce uniqueness, including on a subset with partial indexes
- Shrink indexes with partialFilterExpression
- Expire data with TTL
- Know when to use text, wildcard, hashed, 2dsphere and hidden indexes
conceptThe concept
| Type / option | Use | Watch out for |
|---|---|---|
unique: true | Natural keys (email, orderNo) | Missing fields count as null; two missing values collide unless partial |
partialFilterExpression | Index only documents matching a filter (active users, unpaid orders) | Query must include the filter condition to use it |
sparse: true | Skip documents missing the field | Superseded by partial indexes; can return incomplete results for sorts |
expireAfterSeconds (TTL) | Sessions, logs, OTPs | Single date field; deletion runs every 60s |
"text" | Keyword search with stemming | One per collection; no fuzzy matching or relevance tuning like a search engine |
"$**" wildcard | Arbitrary or user-defined fields | No multi-field sort support; larger than targeted indexes |
"hashed" | Hashed shard keys, even distribution | Equality only, no ranges |
"2dsphere" | Geo queries: $near, $geoWithin | GeoJSON format: [lng, lat] order |
hidden: true | Test the impact of dropping an index | Still maintained on writes |
collation | Case-insensitive matching and sorting | Query must specify the same collation |
whyWhy it matters
The right index option often solves a problem more elegantly than application code: uniqueness only among active users (partial unique), automatic cleanup (TTL), or a much smaller index that fits in RAM (partial).
Use caseReal-world scenario
A "one open cart per customer" rule: createIndex({ customerId: 1 }, { unique: true, partialFilterExpression: { status: "open" } }). Closed carts are not indexed, so a customer can have many of them, but never two open ones.
Try itExamples
Run and edit these in the interactive lesson.
db.lab_carts.drop()
db.lab_carts.createIndex({ customerId: 1 }, { unique: true, partialFilterExpression: { status: "open" } })
db.lab_carts.insertMany([{ customerId: 1, status: "closed" }, { customerId: 1, status: "closed" }, { customerId: 1, status: "open" }])
db.lab_carts.countDocuments()db.lab_carts.insertOne({ customerId: 1, status: "open" })db.orders.createIndex({ createdAt: -1 }, { partialFilterExpression: { status: "pending" }, name: "pending_by_date" })
db.orders.find({ status: "pending" }).sort({ createdAt: -1 }).limit(5).explain().queryPlanner.winningPlan
db.orders.find({ status: "paid" }).sort({ createdAt: -1 }).limit(5).explain().queryPlanner.winningPlandb.reviews.createIndex({ title: "text", body: "text" }, { weights: { title: 3 } })
db.reviews.find({ $text: { $search: "battery -packaging" } }, { score: { $meta: "textScore" }, title: 1, _id: 0 }).sort({ score: { $meta: "textScore" } }).limit(3)db.events.createIndex({ sessionId: "hashed" })
db.events.find({ sessionId: db.events.findOne().sessionId }).explain().queryPlanner.winningPlan.inputStage.indexName
db.orders.createIndex({ status: 1 })
db.orders.hideIndex("status_1")
db.orders.getIndexes().filter(i => i.hidden)internalsUnder the hood
Unique indexes are checked on every insert and update; concurrent inserts of the same key are serialized on that key. Partial indexes contain only matching documents, so they are smaller and cheaper to maintain. TTL deletion is done by a background thread on the primary; the deletes replicate like any other delete. Hidden indexes are not considered by the planner but are still updated, so unhiding is instant.
Avoid / preferDo this, not that
db.customers.createIndex({ email: 1 }, { unique: true, sparse: true })db.customers.createIndex({ email: 1 },
{ unique: true, partialFilterExpression: { email: { $type: "string" } } })Partial indexes say exactly which documents are indexed and are a superset of sparse. Sparse compound indexes in particular behave in ways few people predict.
db.sessions.insertOne({ token: "t1", expiresAt: "2026-10-09T00:00:00Z" })
db.sessions.createIndex({ expiresAt: 1 }, { expireAfterSeconds: 0 })db.sessions.insertOne({ token: "t1", expiresAt: new Date("2026-10-09T00:00:00Z") })
db.sessions.createIndex({ expiresAt: 1 }, { expireAfterSeconds: 0 })TTL only acts on Date values (or arrays of them). A string date is never expired, so the collection grows forever.
PitfallsCommon mistakes
- A unique index on an optional field without a partial filter: the second document without the field fails with E11000 on null.
- Partial index created but queries do not include the partial condition, so it is never used.
- Expecting TTL deletion to be exact to the second.
- Using a text index as a full search engine (no typo tolerance, autocomplete, facets or analyzers per language field).
- Storing geo points as
[lat, lng]; GeoJSON is[longitude, latitude].
LimitsWhen not to use it
Wildcard and text indexes do not replace designed compound indexes for known queries. A hashed index cannot serve range queries or sorts. TTL deletion is not precise: the monitor runs every 60 seconds and can fall behind under load, so do not rely on it for exact expiry times.
SecuritySecurity notes
A unique index on email with the default collation treats Admin@x.com and admin@x.com as different, which allows look-alike duplicate accounts. Normalize emails to lower case before writing, or build the unique index with a case-insensitive collation (strength: 2).
ChallengeTry it yourself
In a scratch collection carts_lab, allow only one open cart per customer while allowing any number of closed carts. Prove that a second open cart is rejected.
Show one solution
db.carts_lab.drop()
db.carts_lab.createIndex({ customerId: 1 }, { unique: true, partialFilterExpression: { status: "open" } })
db.carts_lab.insertMany([{ customerId: 1, status: "closed" }, { customerId: 1, status: "closed" }, { customerId: 1, status: "open" }])
try { db.carts_lab.insertOne({ customerId: 1, status: "open" }) } catch (e) { print("rejected:", e.code) }
db.carts_lab.countDocuments()InterviewQuestions you should be able to answer
Partial vs sparse index?
Sparse indexes skip documents that lack the indexed field. Partial indexes skip documents that do not match an arbitrary filter expression (existence, equality, ranges, $type), so they are a superset of sparse functionality and are preferred. Both may be ignored by the planner if using them would return incomplete results.
How do you safely drop an index you think is unused?
Check $indexStats on every member (counters are per node and reset on restart), hide it with hideIndex(), watch latency and the slow query log for a full business cycle (including month-end jobs), then drop it. If something regresses, unhideIndex() is instant because the index was maintained while hidden.
QuizCheck your understanding
4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.