# Collation-based case-insensitive indexes as a $regex alternative > Source-anchored research for Collation-based case-insensitive indexes as a $regex alternative. Run status: COMPLETED. Parent: [MongoDB Query Optimization Tooling: Compass Explain, Atlas Query Insights and Performance Advisor](https://llms-explorer.com/tree/mongodb-query-optimization-tooling-compass-explain-atlas-query-insights-and-performance-advisor/) · 4 facets · 12 facts · page: https://llms-explorer.com/tree/collation-based-case-insensitive-indexes-as-a-regex-alternative/ ## Definitions - The $regex query predicate is not collation-aware, so a case-insensitive index does not accelerate it through case-insensitive collation keys; adding collation to a regex query does not turn it into an indexed collated-equality lookup. (high confidence) — [source](https://www.mongodb.com/docs/manual/core/index-case-insensitive.md) - Collated equality is a scoped alternative for whole-value matching, not a general replacement for prefix, substring, or arbitrary regex patterns. Separately, case-sensitive regex patterns beginning with a simple literal prefix can support index range optimization; that documented optimization should not be assumed for /prefix/i. (high confidence) — [source](https://www.mongodb.com/docs/manual/core/index-case-insensitive.md) - Case insensitivity is a comparison policy, not a blanket equivalence rule for every script or accent. In Codevra's English strength-two example, Ada and ada compare equally while Áda remains distinct; test the application's locale and representative values before replacing regex. (medium confidence) — [source](https://www.mongodb.com/docs/manual/core/index-case-insensitive.md) ## How it works - For whole-value case-insensitive equality, create an index such as {name:1} with collation {locale:'en',strength:2}, and run find({name:'ada'}).collation({locale:'en',strength:2}); this is an alternative when regex was being used only to express case-insensitive equality. (high confidence) — [source](https://www.mongodb.com/docs/manual/core/index-case-insensitive.md) - The incident's published explain results show why IXSCAN alone is insufficient: the collation-index plan examined 1,902,678 documents for one result, while the hinted ordinary-index plan examined the same number of keys but fetched one document. Inspect indexName, bounds, totalKeysExamined, totalDocsExamined and nReturned when evaluating the rewrite. (medium confidence) — [source](https://www.mongodb.com/community/forums/t/collation-index-best-practice/2189) - When collated and binary indexes coexist and a regex query performs poorly, inspect explain('allPlansExecution'), index bounds, keys examined, documents examined, and returned results. A measured hint to the binary index helped the historical Parse incident, but that workaround does not establish a universal rule for current query planners. (medium confidence) — [source](https://github.com/parse-community/parse-server/issues/6559) ## Comparisons and alternatives - For a whole-value lookup whose required equivalence agrees with the chosen locale, use equality with a matching case-insensitive collation instead of encoding equality as a case-insensitive regex. MongoDB documents createIndex({type:1},{collation:{locale:'en',strength:2}}) and find({type:'apple'}).collation({locale:'en',strength:2}) matching apple, Apple, and APPLE through that index. (high confidence) — [source](https://www.mongodb.com/docs/manual/core/index-case-insensitive.md) ## Facts and statements - String comparisons must use the index's matching collation to benefit from that index. An index's collation alone does not change query comparison rules; collection defaults can supply the collation to both queries and indexes. (high confidence) — [source](https://www.mongodb.com/docs/manual/core/index-case-insensitive.md) - In an April 2020 Parse Server 4.1.0 / MongoDB 3.6.12 incident, operators reported a regex performance regression after adding case-insensitive indexes; hinting the ordinary index restored their prior behavior. This is historical workload evidence, not a claim that current MongoDB releases always select the wrong index. (medium confidence) — [source](https://github.com/parse-community/parse-server/issues/6559) - An operation must use the same collation as the index to use that index for the relevant string comparison. Merely creating a case-insensitive index does not make an otherwise binary equality query case-insensitive. (high confidence) — [source](https://www.mongodb.com/docs/manual/core/index-case-insensitive.md) - Queries and subsequently created indexes inherit a collection's default collation unless they explicitly specify a different collation. With no applicable collection default, a query must explicitly supply the matching non-simple collation to benefit from that index's string comparison rules. (high confidence) — [source](https://www.mongodb.com/docs/manual/core/index-case-insensitive.md) - A historical Parse Server 4.1.0 incident on MongoDB 3.6.12 reported a regex query selecting a collated index and slowing down. The accompanying MongoDB forum explain shows IXSCAN plus FETCH with 1,902,678 keys and documents examined for one returned document. This demonstrates that seeing IXSCAN does not itself establish an efficient selective lookup; these are historical observations, not a claim about current server versions. (medium confidence) — [source](https://github.com/parse-community/parse-server/issues/6559)