skip to content

Why does a MongoDB query ignore an index built with a non-default collation?

level: middleimportance: nice to knowfreq 24%

answer

  1. An index is sorted; the collation decides the order
  2. Simple means raw byte comparison
  3. The query's rules must match the index's rules
  4. Unspecified does not mean none — the collection has a default
  5. strength 2 folds case but keeps accents

basics

~20 s

An index built with a collation stores keys ordered and compared by that collation's rules. A query can only use it when the operation's collation matches the index's, so a query running under the default simple collation falls back to a collection scan.

solid answer

~50 s

Collation defines locale-aware string comparison: `{ locale: "en", strength: 2 }`, for example, makes comparison case-insensitive. An index created with a collation stores its keys in **that** ordering, so it is only usable by an operation whose collation is identical. If the collection's default is the simple binary collation and the query does not pass `.collation(...)`, the operation runs under simple rules, the keys in the index are ordered differently, and the planner cannot use it — you get a `COLLSCAN`. Two clean fixes: create the **collection** with the collation as its default, so every operation inherits it and the index matches automatically, or pass the same collation on every query that should use the index. A third option, common in practice, is to store a normalized lowercase field and index that with the simple collation.

code

javascript · 7 lines
javascript
db.users.createIndex({ lastName: 1 }, { collation: { locale: "en", strength: 2 } })

// IXSCAN: collation matches the index
db.users.find({ lastName: "smith" }).collation({ locale: "en", strength: 2 })

// COLLSCAN: runs under the simple collation
db.users.find({ lastName: "smith" })

go deeper

for a junior

Know that collation controls how strings are compared, that strength 2 gives case-insensitive matching, and that a query must ask for the same collation the index was built with.

for a middle

Explain that the index is sorted under its collation so a mismatched operation cannot use it, and walk the resolution order: operation, then collection default, then simple.

for a senior

Diagnose the COLLSCAN-despite-a-matching-index symptom from explain output, and choose deliberately between a collection-level default, per-query collation, and a normalized lowercase field.

for a principal

Own it as a schema decision made once: the collection default cannot be changed later, so internationalisation requirements have to be settled before the collection exists or paid for with a migration.

## What collation is Comparing strings byte by byte is only correct for ASCII in one language. Real applications want `"Ana"` and `"ana"` to be the same name, `"ä"` to sort next to `"a"` in German, and `"item10"` to sort after `"item9"`. A **collation** is a set of locale-aware comparison rules: a `locale`, a `strength`, and options such as `caseLevel` and `numericOrdering`. The strength levels that matter most: - **1** — compares base characters only; case and accents both ignored. - **2** — base characters plus accents; case-insensitive but accent-sensitive. - **3** — base characters, accents and case. This is the usual default for a locale. The absence of a collation means the **simple** binary collation: raw byte comparison, no locale awareness at all. That is MongoDB's default for a collection unless you say otherwise. ## Why a mismatch kills index usage An index is a sorted structure. Which order it is sorted in is determined by the collation in force when it was built: ```javascript db.users.createIndex({ lastName: 1 }, { collation: { locale: "en", strength: 2 } }) ``` Under that collation `"Smith"` and `"smith"` compare equal and sit together. Under the simple collation they are different keys in different places, since `S` and `s` are different bytes. So an index built with one collation is simply the wrong data structure for a query running under another. An equality lookup would miss matches, and a range scan or a sort would produce the wrong order. The planner does not attempt any translation: **it uses the index only when the operation's collation is the same as the index's**, and otherwise it plans without it. ```javascript // Uses the index db.users.find({ lastName: "smith" }).collation({ locale: "en", strength: 2 }) // COLLSCAN: runs under the simple collation, index does not match db.users.find({ lastName: "smith" }) ``` ## How collation is resolved for an operation The rules are worth knowing precisely, because "I didn't specify one" does not mean "none": 1. If the operation specifies a collation, that one is used. 2. Otherwise the **collection's default collation** applies, set when the collection was created. 3. If the collection has no default, the simple collation applies. This is why the most robust fix is to set the collation on the collection at creation time: ```javascript db.createCollection("users", { collation: { locale: "en", strength: 2 } }) ``` Every index built afterwards inherits that collation unless overridden, and every query inherits it too, so index and query always agree without a per-call discipline nobody will maintain. The catch is that a collection's default collation cannot be changed later — you would have to create a new collection and migrate. ## Where collation does and does not apply Collation governs comparison and ordering of **strings**: equality matches, `$gt`/`$lt` ranges, sorts, and unique-index key comparison. It does not affect numbers, dates or other BSON types, and it does not change how documents are stored. One genuinely useful consequence: a **unique index with strength 2** makes uniqueness case-insensitive. `"[email protected]"` and `"[email protected]"` become the same key, so the second insert is rejected — usually exactly the behaviour you want for logins, and much less fragile than remembering to lowercase in application code. Aggregation follows the same resolution rules; `aggregate()` takes a `collation` option, and stages that compare strings — `$match`, `$sort`, `$group` keys — honour it. ## The pragmatic alternative Many teams skip collation entirely and store a normalized field: keep `email` as typed for display and `emailLower` for lookups, index `emailLower` with the simple collation, and query it after lowercasing in the application. It costs a little space and one more thing to keep in sync on write, but it needs no per-query discipline, sorts predictably, and never silently degrades to a collection scan when someone forgets an option. Collation is the better answer when you need real linguistic ordering — sorting names for a German or Turkish audience, where lowercasing is not the same as collating — and the normalized-field trick is often the better answer for plain case-insensitive lookup. ## Diagnosing it The symptom is always the same: a perfectly good index that `getIndexes()` shows, an obviously matching query, and `explain("executionStats")` reporting a `COLLSCAN`. Check the index's `collation` field and compare it with what the operation resolves to. A mismatch in any option, including `strength`, is enough.

  • What is the most robust way to make case-insensitive lookups always use the index?
    Create the collection with the collation as its default, for example db.createCollection("users", { collation: { locale: "en", strength: 2 } }). Indexes and queries then inherit it, so they always agree without every call site remembering to pass .collation(). The trade-off is that a collection's default collation cannot be changed afterwards; switching means creating a new collection and migrating.
  • How can collation make a unique index case-insensitive?
    Build the unique index with a collation of strength 2. Keys are compared case-insensitively, so "[email protected]" and "[email protected]" produce the same index key and the second insert fails with a duplicate key error. That enforces case-insensitive uniqueness in the database rather than relying on every write path to lowercase the value first.
  • When would you store a normalized lowercase field instead of using collation?
    When the requirement is plain case-insensitive matching rather than linguistic ordering. An emailLower field indexed with the simple collation needs no per-query option, cannot silently degrade to a collection scan when someone forgets one, and is easy to reason about. Collation earns its complexity when you need correct locale-specific sorting or accent handling.

saying these in an interview costs you the question

  • Assumes a collation index works for any query on that field
  • Thinks collation changes how documents are stored
  • Believes an unspecified collation means no rules apply
  • Says strength 2 ignores accents as well as case
  • Expects collation to affect comparisons of numbers or dates

context

resolving links…