# MongoDB Compound Index Design: ESR, Selectivity, Covering and Multikey Bounds > A compound index stores references ordered by several fields; key order (equality fields first, then sort, then range — the ESR guideline), selectivity, prefix rules, covered queries, multikey index bounds with $elemMatch and partial/sparse filter semantics decide whether a query can use the index and how many keys and documents it examines. Compiled from MongoDB manual and Atlas pages plus the run's verified claims. Parent: [MongoDB Indexes Deep Dive](https://llms-explorer.com/tree/mongodb-indexes-deep-dive/) · 9 facets · 675 facts · page: https://llms-explorer.com/tree/mongodb-compound-index-design-esr-selectivity-covering-and-multikey-bounds/ ## Definitions - Verify an index ordering with explain('executionStats'). A SORT stage in the winning plan means an in-memory sort. Compare keys and documents examined against documents returned. Use cursor.hint() to force a candidate index while testing. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - Index prefixes are the beginning subsets of indexed fields. Compound indexes support queries on all fields included in the index prefix. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#index-prefixes) - Diagnose with explain('executionStats'): a combined range in indexBounds on the array path means bounds were intersected. A single open-ended bound plus a FETCH filter, or keysExamined far above nReturned, means they were not. multiKeyPaths shows which path prefixes make the index multikey. In Pachot's demo, the no-$elemMatch query examined 44 keys with a FETCH filter; with $elemMatch it examined 38 with a single combined range. — [source](https://dev.to/franckpachot/elemmatch-and-multi-key-indexes-i6e) - - The query does not return the array field (meaning the array is not included in the query projection). This means that to cover a query, the multikey index must be [compound.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-compound_multikey_indexes) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#covered-queries) - Since `{ a : 1 }` is a prefix of `{ a : 1, b : 1 }`, Performance Advisor suggests only `{ a : 1, b : 1 }`. To learn more about index prefixes, see [Prefixes.](https://www.mongodb.com/docs/manual/core/index-compound/#prefixes) — [source](https://www.mongodb.com/docs/atlas/performance-advisor/index-ranking.md#index-de-duplication) - For a compound index where the index prefix keys are not strings, arrays, and embedded documents, an operation that specifies a different collation can still use the index to support comparisons on the index prefix keys. — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex.md#create-indexes-with-collation) - Partial indexes with an $or filter are a known planner weak spot: forum reports (MongoDB 6.0.4 and 6.0.13) show a query repeating the identical $or still got COLLSCAN or another index, while a query with plain conditions (e.g. comments:{$exists:true}) used it. No accepted fix is shown on the threads. — [source](https://www.mongodb.com/community/forums/t/partial-index-not-covering-or-query-filter/220425) - The query does not return the array field (meaning the array is not included in the query projection). This means that to cover a query, the multikey index must be compound. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - Since { a : 1 } is a prefix of { a : 1, b : 1 }, Performance Advisor suggests only { a : 1, b : 1 }. To learn more about index prefixes, see Prefixes. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/index-ranking/) - Not all index types can cover queries. For details on covered index support, refer to the documentation page for the corresponding index type. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - Index `{ item: 1, price: 1 }` is a [partial index](https://www.mongodb.com/docs/manual/core/index-partial.md#std-label-index-type-partial) and only indexes documents with `price` field greater than or equal to `Decimal128("10")`. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/planCacheStats.md#mongodb-shell) - Query selectivity refers to how well the query predicate filters out documents in a collection. Query selectivity determines whether queries can use indexes effectively. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - The document with `_id` equal to `3` is the only document that matched the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) criteria. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#elemmatch-https-www-mongodb-com-docs-manual-reference-operator-projection-elemmatch-md-mongodb-projection-proj-elemmatch-with-multiple-fields) ## Structure and components - If the sort keys correspond to the index keys or an index *prefix*, MongoDB can use the index to sort the query results. A *prefix* of a compound index is a subset that consists of one or more keys at the start of the index key pattern. — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#sort-and-index-prefix) - The order of the indexed fields impacts the effectiveness of a compound index. Compound indexes contain references to documents according to the order of the fields in the index. To create efficient compound indexes, follow the [ESR (Equality, Sort, Range) guideline.](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline.md#std-label-esr-indexing-guideline) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#field-order) - The preceding index is multikey because the `genres` field contains array values. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#covered-queries) - However, if the shard key index is a prefix of a compound index, the compound index may become a compound multikey index if one of the trailing keys (that are not part of the shard key) indexes an array. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - However, if the shard key index is a [prefix](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-compound-index-prefix) of a compound index, the compound index may become a compound *multikey* index if one of the trailing keys (that are not part of the shard key) indexes an array. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#shard-keys) - **Compound indexes** are indexes that contain references to multiple fields. Compound indexes improve performance for queries on exactly the fields in the index or fields in the [index prefix.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-compound-index-prefix) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/create-compound-index.md) - Compound indexes can contain different types of sparse indexes. The combination of index types determines how the compound index matches documents. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#sparse-compound-indexes) - The preceding query uses `$elemMatch` to return documents where the `grades` array contains at least one element that matches *both* of the specified conditions. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-bounds-intersection) - The preceding query uses `$elemMatch` on the `ratings` field to require that the array contains at least one *single* element that matches both conditions. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example) - For each distinct value in the array, MongoDB creates a separate entry in the index, and each entry points back to the same document. As a result, a single document can have multiple entries in a multikey index. If an array contains multiple instances of the same value, the index only includes one entry for the value. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - For example, consider a query for `"item": "saccharomyces cerevisiae"` and `"stock": 60`. If the collection contains 10000 documents matching `"item": "saccharomyces cerevisiae"` and only 100 of those documents match `"stock": 60`, the query examines 10000 keys. In the `IXSCAN` stage, the query filters those keys by the `stock` field and only returns 100 results to the next stage. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#index-prefixes) - When an index [covers](https://www.mongodb.com/docs/manual/core/query-optimization.md#std-label-indexes-covered-queries) a query, MongoDB can both match the query conditions **and** return the results using only the index keys. MongoDB does not need to examine documents from the collection to perform any part of the query. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#covered-queries) - With the compound index on `department` and `description`, the query only examines **one** index key. There is only one document in the collection where `department` is `kitchen` and the `description` contains the string `green`. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text/limit-number-of-items-scanned-for-text-search.md#view-number-of-documents-examined) - The bounds of an index scan define the parts of an index to search during a query. The computation of multikey index bounds follows special rules. For details, see Multikey Index Bounds. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - The bounds of an index scan define the parts of an index to search during a query. The computation of multikey index bounds follows special rules. For details, see [Multikey Index Bounds.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#std-label-indexes-multikey-bounds) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#index-bounds) - [Sparse indexes](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) only contain entries for documents that have the indexed field. These indexes skip documents that do not have the indexed field. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-properties.md#sparse-indexes) - A sparse index contains entries only for documents that have the indexed field, even when the value is null. Documents missing the field are skipped. A non-sparse index stores null for those documents. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md) - Specifies the number of "work units" performed by the query execution stage. Query execution divides its work into small units. A "work unit" might consist of examining a single index key, fetching a single document from the collection, applying a projection to a single document, or doing a piece of internal bookkeeping. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#unsharded-collections) - | Field | Description | Applicable Stages | | --- | --- | --- | | `docsExamined` | Specifies the number of documents scanned during the query execution stage. | `COLLSCAN`, `FETCH` | | `keysExamined` | For query execution stages that scan an index `keysExamined` is the total number of in-bounds and out-of-bounds keys that are examined in the process of the index scan. If the index scan consists of a single contiguous range of keys, only in-bounds keys need to be examined. If the index bounds consists of several key ranges, the index scan execution process may examine out-of-bounds keys in orde — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#unsharded-collections) - The index does not cover the following query because the projection contains the `genres` array field: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#covered-queries) - 1. The `movies` collection contains documents where the `metacritic` field is present in some documents and absent from others. Of the 21,349 documents, 6,964 have the `metacritic` field and 14,385 do not. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/exists.md#use-a-sparse-index-to-improve-exists-performance) - - All the fields in the query (both as specified by the application and as needed internally such as for sharding purposes) are part of an index. — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#run-covered-queries) - Because the index contains all fields required by the query, MongoDB can both match the [query conditions](https://www.mongodb.com/docs/manual/tutorial/query-documents.md#std-label-read-operations-query-document) and return the results using only the index. — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#performance) - When run on [`mongos`](https://www.mongodb.com/docs/manual/reference/program/mongos.md#std-program-mongos), indexes can only cover queries on [sharded](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-shard) collections if the index contains the shard key. — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#sharded-collections) - | Field | Description | | --- | --- | | `db` | The output database name. For a [replica set](https://www.mongodb.com/docs/manual/replication.md#std-label-replica-set) or a standalone, if the output database does not exist, `$out` also creates the database. | | `coll` | The output collection name. | | `timeseries` | A document that specifies the configuration to use when writing to a time series collection. The `timeField` is required. All other fields are optional. | | `timeField` | Required when writing to a time series collection. The name of the field which contains the date in each time se — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/out.md#syntax) - | Field | Type | Description | | --- | --- | --- | | `timeseries.timeField` | string | Required. The name of the field which contains the date in each time series document. Documents in a time series collection must have a valid BSON date as the value for the `timeField`. | | `timeseries.metaField` | string | Optional. The name of the field which contains metadata in each time series document. The metadata in the specified field should be data that is used to label a unique series of documents. The metadata should rarely, if ever, change The name of the specified field may not be `_id` or the — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-procedures.md#time-series-field-reference) - If your application repeatedly runs a query that contains multiple fields, you can create a compound index to improve performance for that query. For example, a grocery store manager often needs to look up inventory items by name and quantity to determine which items are low stock. You can create a compound index on both the `item` and `quantity` fields to improve query performance. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#use-cases) - Starting in MongoDB 5.3, if the query planner selects a [clustered index](https://www.mongodb.com/docs/manual/reference/method/db.createcollection.md#std-label-db.createCollection.clusteredIndex) for a [clustered collection](https://www.mongodb.com/docs/manual/core/clustered-collections.md#std-label-clustered-collections) and the query contains bounds that define the portion of the index to search, the explain result includes a `CLUSTERED_IXSCAN` stage. The stage includes information about the clustered index key and index bounds. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#collection-scan) - The unique index allows the insertion of a document without the `email` field if the collection does not already contain a document missing the `email` field: — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#missing-document-field-in-a-unique-single-field-index) - You do not need to explicitly specify an index as multikey. If you create an index on a field that contains an array value, MongoDB automatically creates the index as a multikey index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - If your application frequently queries a field that contains an array value, a multikey index improves performance for those queries. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - You can create an index on the genres field to improve performance for this query. Because genres contains an array value, MongoDB stores the index as a multikey index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - For example, you can create a compound multikey index { genres: 1, year: 1 } on the movies collection because for each document, only one field indexed by the compound multikey index is an array. No document contains array values for both genres and year fields. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - If the query does not specify an equality condition on an index prefix that precedes or overlaps with the sort specification, the operation will not efficiently use the index. For example, the following operations specify a sort document of { c: 1 }, but the query documents do not contain equality matches on the preceding index fields a and b: — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) - A document that provides metrics on queries where the slot-based query execution engine uses path arrayness data to optimize leading filters. Path arrayness data indicates whether a query path can contain an array, and comes from [multikey index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multikey) metadata. For more information on the slot-based query execution engine, see [Slot-Based Query Execution Engine Pipeline Optimizations.](https://www.mongodb.com/docs/manual/core/aggregation-pipeline-optimization.md#std-label-sbe-pipeline-optim — [source](https://www.mongodb.com/docs/manual/reference/command/serverStatus.md#metrics) - MongoDB does not support unique indexes across shards, except when the unique index contains the full shard key as a prefix of the index. In these situations MongoDB will enforce uniqueness across the full key, not a single field. — [source](https://www.mongodb.com/docs/manual/reference/limits.md#sharding-operational-restrictions) - Because `genres` contains an array value, MongoDB stores this index as a multikey index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/create-multikey-index-basic.md#procedure) - The index **does not** support queries on only the `metacritic` field, because `metacritic` is not part of the index prefix. For example, the index does not support this query: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/create-compound-index.md#results) - Because `stock` contains an array value, MongoDB stores this index as a multikey index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/create-multikey-index-embedded.md#procedure) - If you count the number of documents in the `movies` collection and include a hint that specifies that sparse index, the operation returns only the documents that contain the `rated` field. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#sparse-index-and-incomplete-results) - - If an index is not used, the `{ $meta: "indexKey" }` expression does not return a value and the field is not included as part of the output. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/meta.md#return-value) - Each stage consists of information specific to the stage. For example, an `IXSCAN` stage includes the index bounds along with other data specific to the index scan. If a stage has a child stage or multiple child stages, the stage will have an `inputStage` or `inputStages`. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#unsharded-collections) - Each stage consists of information specific to the stage. For example, an `IXSCAN` stage includes the index bounds along with other data specific to the index scan. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#unsharded-collections) - Indexes are special data structures that store a small portion of the collection's data set in an easy-to-traverse form. MongoDB indexes use a [B-tree](https://en.wikipedia.org/wiki/B-tree) data structure. — [source](https://www.mongodb.com/docs/manual/indexes.md#details) - If you drop an index that is actively used in production, your application may incur a performance degradation. To ensure queries can still use an index during modification, you can create a temporary, redundant index that contains the same fields as the modified index. — [source](https://www.mongodb.com/docs/manual/tutorial/manage-indexes.md#minimize-performance-impact-with-a-temporary-index) - | Scenario | Performance insight | | --- | --- | | You run a query or aggregation without an index. | Add an index to support the operation. | | You run an aggregation pipeline that uses a [`$lookup`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup.md#mongodb-pipeline-pipe.-lookup) stage. | Embed related data to avoid the need for a `$lookup` operation. | | You run a [`$text`](https://www.mongodb.com/docs/manual/reference/operator/query/text.md#mongodb-query-op.-text) or [`$regex`](https://www.mongodb.com/docs/manual/reference/operator/query/regex.md#mongodb-query-op. — [source](https://www.mongodb.com/docs/atlas/atlas-ui/performance-insights.md#behavior) - When you filter and sort by a field that contains an array, the filter does not affect the value used as the [sort key](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-sort-key). The sort always considers all array values as potential sort keys. — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.sort.md#filter-and-sort-by-an-array-field) - The following query uses the ESR (Equality, Sort, Range) rule to structure a more efficient aggregation pipeline and improve query response times. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - The [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) operator matches documents that contain an array field with at least one element that matches all the specified query criteria. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#definition) - The document with an `_id` of `5` doesn't contain an array. The document is included to show that `$elemMatch` matches only array elements. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#array-of-embedded-documents) - - The `borough` field contains a string indicating the borough in which the restaurant is located. The cardinality is low enough that Compass can provide a graded bar of the field contents, with the most-frequently occurring string on the left. — [source](https://www.mongodb.com/docs/compass/schema.md) - For each field, Compass displays summary information about the data type or types the field contains and the range of values. Depending on the data type and the level of cardinality, Compass displays histograms, graded bars, geographical maps, and sample data to provide a sense of the shape and scope of the data contained in each field. — [source](https://www.mongodb.com/docs/compass/schema.md#field-descriptions) - The [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) operator limits the contents of an `` field from the query results to contain only the **first** element matching the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) condition. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#definition) - - For the document with `_id` equal to `1`, the `students` array contains multiple elements with the `school` field equal to `102`. However, the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) projection returns only the first matching element from the array. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#zipcode-search) - - The document with `_id` equal to `3` does not contain the `students` field in the result since no element in its `students` array matched the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) condition. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#zipcode-search) - The document with `_id` equal to `3` does not contain the `students` field since no array element matched the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) criteria. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#elemmatch-https-www-mongodb-com-docs-manual-reference-operator-projection-elemmatch-md-mongodb-projection-proj-elemmatch-with-multiple-fields) - If your workload has high cardinality, flattening nested objects may improve performance. For example, the following document contains the same data as the previous document but in a flattened format: — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-best-practices.md#use-standard-embedded-fields-best-practices) - The following example uses the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) and [`$ne`](https://www.mongodb.com/docs/manual/reference/operator/query/ne.md#mongodb-query-op.-ne) operators to query for documents where `languages` contains at least one element that is both not `null` and does not equal `English`: — [source](https://www.mongodb.com/docs/manual/tutorial/query-arrays.md#query-for-an-array-element-that-meets-multiple-criteria) - Contains counts for each cardinality estimation method used during cost-based ranking. Each field represents the number of times a particular estimation method was used. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats/) - For fields that contain arrays, MongoDB provides the following projection operators for manipulating arrays: [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch), [`$slice`](https://www.mongodb.com/docs/manual/reference/operator/projection/slice.md#mongodb-projection-proj.-slice), and [`$`.](https://www.mongodb.com/docs/manual/reference/operator/projection/positional.md#mongodb-projection-proj.-) — [source](https://www.mongodb.com/docs/manual/tutorial/project-fields-from-query-results.md#project-specific-array-elements-in-the-returned-array) - If the field contains an array of documents, you can use the [`$all`](https://www.mongodb.com/docs/manual/reference/operator/query/all.md#mongodb-query-op.-all) with the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) operator. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/all.md#use-all-with-elemmatch) - If the compound query conditions on an array field do not use the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) operator, the query selects those documents whose array contains any combination of elements that satisfies the conditions. — [source](https://www.mongodb.com/docs/manual/tutorial/query-array-of-documents.md#combination-of-elements-satisfies-the-criteria) ## How it works - A third-party blog lists further exceptions: a covered query may justify range before sort when the result set is small, and a low-cardinality equality field needs careful placement. These are practitioner advice, not in the MongoDB manual. — [source](https://oneuptime.com/blog/post/2026-03-31-mongodb-esr-rule-compound-index/markdown) - The fields `"ratings.scores.q1"` and `"ratings.scores.q2"` share the field path `"ratings.scores"`. In order to compound index bounds, a query must use `$elemMatch` on the common field path. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-elemmatch-on-diverging-field-paths) - Compound bounds combine bounds for multiple keys of a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound). Using bounds from multiple keys reduces the time it takes to process a query because MongoDB does not need to compute results for each bound individually. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#compound-bounds-for-a-multikey-index) - MongoDB's manual defines unnecessary indexes as three kinds: rarely used, redundant because another compound index covers them, or not used at all. Indexing every field or every query bloats the collection and hurts write performance. — [source](https://mongodb.com/docs/manual/data-modeling/design-antipatterns/unnecessary-indexes/) - In explain, an index-only plan shows PROJECTION_COVERED over IXSCAN with totalDocsExamined 0. A default projection that includes _id, or a partial projection, shows FETCH then PROJECTION_SIMPLE. Projecting _id forces a fetch because the index's $recordId is not _id. — [source](https://dev.to/franckpachot/index-only-scan-on-json-documents-with-covering-and-multi-key-indexes-in-mongodb-53h4) - Because the constraint applies to separate documents, for a unique [multikey](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multikey) index, a document may have array elements that result in repeating index key values as long as the index key values for that document do not duplicate those of another document. In this case, the repeated index entry is inserted into the index only once. — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#unique-constraint-across-separate-documents) - An index supports a sort on a subset of its keys only when the query has equality conditions on all prefix keys that precede the sort keys. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - Multikey indexes can cover queries over the non-array fields if the index tracks which field or fields cause the index to be multikey. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - Bounds intersection refers to the point where multiple bounds overlap. For example, given the bounds `[ [ 3, Infinity ] ]` and `[ [ -Infinity, 6 ] ]`, the intersection of the bounds results in `[ [ 3, 6 ] ]`. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#bounds-intersection-for-a-multikey-index) - Because the query uses `$elemMatch` to join these predicates, MongoDB intersects the bounds to: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-bounds-intersection) - Because the preceding query does not use `$elemMatch`, MongoDB does not intersect the bounds. Instead, MongoDB uses either of the following bounds: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#query-without-elemmatch) - The following example shows how MongoDB uses compound bounds when an index includes a non-array field and multiple array fields. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-compound-bounds-of-non-array-field-and-multiple-array-fields) - Do not rely on the stage name alone to detect coverage. A forum reply reports a covered query shows plain PROJECTION on 4.0 and PROJECTION_COVERED on 5.0. totalDocsExamined 0 is the more dependable check, except when the query returns no results. — [source](https://mongodb.com/community/forums/t/adding-the-projected-fields-to-compound-index-to-create-covered-query-doesnt-improve-performance/199267) - MongoDB intersects bounds on a multikey array field only when $elemMatch joins the predicates. Example: grades: {$elemMatch: {$gte: 90, $lte: 99}} yields bounds [[90, 99]]. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md) - When you sort based on an array field that is indexed with a multikey index, the query plan includes an in-memory sort stage unless both of the following are true: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - When you sort based on an array field that is indexed with a [multikey index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multikey), the query plan includes an [in-memory sort](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-in-memory-sort) stage unless both of the following are true: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#sorting) - | Scenario | Index Type | | --- | --- | | A human resources department often needs to look up employees by employee ID. You can create an index on the employee ID field to improve query performance. | [Single Field Index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-single.md#std-label-indexes-single-field) | | A salesperson often needs to look up client information by location. Location is stored in an embedded object with fields like `state`, `city`, and `zipcode`. You can create an index on the `location` object to improve performance for queries on that object. When y — [source](https://www.mongodb.com/docs/manual/indexes.md#use-cases) - When an index covers a query, the explain result has an `IXSCAN` stage that is **not** a descendant of a `FETCH` stage. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#covered-queries) - When [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) generates index keys on a field that holds an array, [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) generates an index key for each array element. For compound indexes, [`mongod`](https://www.mongodb.com/docs/manual/reference/program/mongod.md#mongodb-binary-bin.mongod) calculates the [cartesian product](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-cartesian-product) of the sets of keys that are generated for eac — [source](https://www.mongodb.com/docs/manual/reference/limits.md#indexes) - In a unique multikey index, a document may have array elements that result in repeating index key values as long as the index key values for that document do not duplicate those of another document. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - Even though the sort is by the indexed field, if some documents in the `movies` collection do not have a `plot` field, MongoDB does **not** select the sparse index to fulfill the query in order to return complete results. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#sparse-index-on-a-collection-cannot-return-complete-results) - A multikey index stores one entry per distinct array element, each pointing to the same document. MongoDB marks an index multikey automatically when an indexed field holds an array. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md) - - `heuristics`: Uses a set of rules to determine selectivity. Only used when sampling fails. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#unsharded-collections) - For example, the following query can use the index because it includes both a condition on the `name` field and a non-null match on the `email` field: — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#comparison-with-sparse-indexes) - **Index bounds** define the range of index values that MongoDB searches when using an index to fulfill a query. When you specify multiple query predicates on an indexed field, MongoDB attempts to combine the bounds for those predicates to produce an index scan with smaller bounds. Smaller index bounds result in faster queries and reduced resource use. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md) - - Only the `title` field, because `title` is a [prefix](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-compound-index-prefix) of the compound index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/create-compound-index.md#results) - Float that represents the estimated number of index keys that the query planner predicts the plan to process. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#unsharded-collections) - An index supports sort operations on a subset of its keys only when the query includes equality conditions on all prefix keys that precede the sort keys. For more information, see Sort and Non-prefix Subset of an Index. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - $indexStats counters reset when mongod restarts, so zero ops after a recent restart is meaningless. Sources advise a long enough uptime to cover every query pattern (Netdata: at least 24 hours as a minimum, and a full workload cycle). — [source](https://www.netdata.cloud/guides/mongodb/mongodb-unused-indexes/) - You can create an index on any field in a document, including top-level fields, embedded fields, or fields inside embedded documents. When you create an index, specify the field and the sort order (`1` for ascending, `-1` for descending). — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-single.md) - MongoDB does not use the partial index for a query or sort operation if using the index results in an incomplete result set. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#query-coverage) - You can create an index on a field containing an array value to improve performance for queries on that field. When you create an index on a field containing an array value, MongoDB stores that index as a multikey index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/create-multikey-index-basic.md) - The query targeting alert typically occurs when there is no index to support a query or queries or when an existing index only partially supports a query or queries. — [source](https://www.mongodb.com/docs/atlas/reference/alert-resolutions/query-targeting.md#common-triggers) - A covered query is a query that can be satisfied entirely using an index and doesn't have to examine any documents. An index covers a query when all of the following apply: — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - You can create indexes on embedded document fields within arrays. These indexes improve performance for queries on specific embedded fields that appear in arrays. When you create an index on a field inside an array, MongoDB stores that index as a multikey index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/create-multikey-index-embedded.md) - If you include a [`hint()`](https://www.mongodb.com/docs/manual/reference/method/cursor.hint.md#mongodb-method-cursor.hint) that specifies a [sparse index](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) when you perform a [`count()`](https://www.mongodb.com/docs/manual/reference/method/cursor.count.md#mongodb-method-cursor.count) of all documents in a collection (i.e. with an empty query predicate), the sparse index is used even if the sparse index results in an incorrect count. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#sparse-index-and-incomplete-results) - When possible, distinct operations can use indexes. — [source](https://www.mongodb.com/docs/manual/reference/command/distinct/) - - A sort on `{ : ±1 }` uses a secondary index on `{ metaField: ±1, timeField: ±1 }` when there is a point predicate on ``. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#use-secondary-indexes-to-improve-sort-performance) - Collation-aware index keys might be larger than index keys for indexes without collation because indexes that are configured with collation use ICU collation keys to achieve sort order. — [source](https://www.mongodb.com/docs/manual/reference/command/createIndexes/) - If the collection has an index that includes the sort fields, MongoDB can use that index to obtain the results of a sort operation. In $or queries, MongoDB may use multiple indexes to support a single sort operation, because each clause of the $or expression can use its own index. — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) - You can only have one clustered index in a collection because the documents can be stored in only one order. Only collections with a clustered index store the data in sorted order. — [source](https://www.mongodb.com/docs/manual/core/clustered-collections.md#behavior) - Although indexes improve query performance, adding an index has negative performance impact for write operations. For collections with a high write-to-read ratio, indexes are expensive because each insert and update must also update any indexes. — [source](https://www.mongodb.com/docs/manual/core/indexes/create-index.md#about-this-task) - The following example demonstrates sorting when index keys have the same or different types. — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) - - If there are secondary indexes for the clustered collection, the collection has a larger storage size. This is because secondary indexes on a clustered collection with large clustered index keys may have a larger storage size than secondary indexes on a non-clustered collection. — [source](https://www.mongodb.com/docs/manual/core/clustered-collections.md#limitations) - You can create indexes suggested by the Performance Advisor directly within the Performance Advisor itself. When you create indexes, keep the ratio of reads to writes on the target collection in mind. Indexes come with a performance cost, but are more than worth the cost for frequent queries on large data sets. To learn more about indexing strategies, see Indexing Strategies. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/) - The following sections explain the index properties that you can specify when building an index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-properties.md) - When you insert data into a time series collection, the internal collection automatically organizes the data into an optimized storage format using [buckets](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-bucketing.md#std-label-timeseries-bucketing-specifics). If a suitable bucket exists, MongoDB inserts new data into that bucket. If a suitable bucket does not exist, MongoDB creates a new bucket. To optimize storage, choose a `metaField` that rarely changes to create time series collections with fewer, more densely packed buckets. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-considerations.md#storage-and-cardinality) - For each open bucket, the catalog maintains information such as the `metaField`, active writers, covered time span, number of documents, size, and recent operations. Because MongoDB creates separate buckets for documents with a different `metaField`, multiple buckets are typically open at the same time. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-bucketing.md#the-bucket-catalog) - The sort direction is descending because the `sortBy` value is "-1". — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sortArray.md#sort-on-a-subfield) - - Ensure that your schema design does not rely on indexed arrays that grow in length without bound. Typically, best performance can be achieved when such indexed arrays have fewer than 1000 elements. — [source](https://www.mongodb.com/docs/manual/administration/production-checklist-development.md#schema-design) - The following sections show the output differences when you use `$elemMatch` with a single query condition, and omit `$elemMatch`. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#single-query-condition) - When unset, `contention` defaults to `8`, which provides high performance for most workloads. Higher contention improves the performance of insert and update operations on low cardinality fields, but decreases find performance. — [source](https://www.mongodb.com/docs/manual/core/queryable-encryption/fundamentals/encrypt-and-query.md#advanced-query-parameters) - Collections with fine-grained or changing `metaField` values generate many sparsely packed, short-lived buckets, increasing the cardinality of your collection. Increasing cardinality leads to decreased storage and query efficiency. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-considerations.md#storage-and-cardinality) - All time series collection use a compressed bucket format when you append data into opened or reopened buckets. Compressing time series data in the cache supports high [cardinality](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-cardinality) workloads while preserving efficient query performance. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-considerations.md#compression-and-hardware) - Finds the distinct values for a specified field across a single collection or view and returns the unique results in an array. — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#definition) - mongos performs a broadcast operation for queries that do not include the shard key, routing queries to all shards in the cluster. Some queries that do include the shard key may still result in a broadcast operation depending on the distribution of data in the cluster and the selectivity of the query. — [source](https://www.mongodb.com/docs/manual/core/sharded-cluster-query-router/) ## How-to and procedures - Create a [compound multikey index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound) on the `item` and `ratings` fields: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-compound-bounds-of-non-array-field-and-array-field) - Create a compound index on the `ratings.score` and the `ratings.by` fields: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example) - Create a compound index on the `ratings.scores.q1` and the `ratings.scores.q2` fields: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-elemmatch-on-diverging-field-paths) - Create a unique compound [multikey](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multikey) index on `email` and `name`: — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#unique-compound-index) - To compound the bounds for `"ratings.score"` with the bounds for `"ratings.by"`, the query must use `$elemMatch`. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-compound-bounds-of-non-array-field-and-multiple-array-fields) - To compound the bounds, the query must use `$elemMatch` on the common path `"ratings.scores"`: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-elemmatch-on-diverging-field-paths) - To achieve a covered query, you must index projected fields. The ESR (Equality, Sort, Range) rule applies to the order of fields in the index. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - To achieve a covered query, you must index projected fields. The [ESR (Equality, Sort, Range) rule](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline.md#std-label-esr-indexing-guideline) applies to the order of fields in the index. — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#example) - To check if MongoDB must perform an in-memory sort, append [`cursor.explain()`](https://www.mongodb.com/docs/manual/reference/method/cursor.explain.md#mongodb-method-cursor.explain) to the query and check the [explain results](https://www.mongodb.com/docs/manual/reference/explain-results.md#std-label-explain-results). If the query plan contains a `SORT` stage, then MongoDB must perform an in-memory sort operation. — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.sort.md#sort-and-index-use) - To learn how MongoDB combines multikey index bounds to improve performance, see Multikey Index Bounds. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - To see whether your operation results in a `DISTINCT_SCAN`, check your operation's [explain results.](https://www.mongodb.com/docs/manual/reference/explain-results.md#std-label-explain-results) — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#retrieve-distinct-values) - To create a `partial` index, use the [`db.collection.createIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.createindex.md#mongodb-method-db.collection.createIndex) method with the `partialFilterExpression` option. The `partialFilterExpression` option accepts a document that specifies the filter condition using: — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#create-a-partial-index) - To learn how to manage indexes in MongoDB Compass, see [Manage Indexes.](https://www.mongodb.com/docs/manual/tutorial/manage-indexes.md#std-label-manage-indexes) — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#create-a-partial-index) - To index fields in embedded documents, use [dot notation](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-dot-notation). See [Create an Index on an Embedded Field.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-single/create-single-field-index.md#std-label-index-embedded-fields) — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#embedded-documents) - Create a compound index to support sorting on multiple fields. — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) - To improve query performance for [time series collections](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-time-series-collection), add one or more [secondary indexes](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-secondary-index) to support common time series query patterns. Starting in MongoDB 6.3, MongoDB automatically creates a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound) on the `metaField` and `timeField` fields for new collections. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md) - Create the following compound index on the `type` and `item` fields: — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/meta.md#meta-indexkey) - Create a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound) to support sorting on multiple fields. — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#sort-on-multiple-fields) - To support the query, add a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound). With compound indexes, the order of the fields matter. — [source](https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan.md#mongodb-shell) - To support the query, add a compound index. With compound indexes, the order of the fields matter. — [source](https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan/) - To create a compound index, use the [`db.collection.createIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.createindex.md#mongodb-method-db.collection.createIndex) method: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/create-compound-index.md) - To create a compound index, see [Create a Compound Index.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/create-compound-index.md#std-label-index-create-compound) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#get-started) - Create indexes for commonly issued queries. If a query searches multiple fields, create a compound index. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - To determine whether a query is a covered query, use the db.collection.explain() or the explain() method. See Covered Queries. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - Create indexes for commonly issued queries. If a query searches multiple fields, create a [compound index.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound) — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#create-indexes-to-support-queries) - To determine whether a query is a covered query, use the [`db.collection.explain()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.explain.md#mongodb-method-db.collection.explain) or the [`explain()`](https://www.mongodb.com/docs/manual/reference/method/cursor.explain.md#mongodb-method-cursor.explain) method. See [Covered Queries.](https://www.mongodb.com/docs/manual/reference/explain-results.md#std-label-explain-output-covered-queries) — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#explain-results) - Create a compound index on the `inventory` collection that contains the following fields: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text/limit-number-of-items-scanned-for-text-search.md#procedure) - Ensure that equality fields always come first. Placing equality fields first keeps the remaining index fields in sorted order. Choose whether to use a sort or range field next based on your index's specific needs: — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - Create a unique compound [multikey](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multikey) index on `a.loc` and `a.qty`: — [source](https://www.mongodb.com/docs/manual/core/index-unique/create-compound.md#compound-unique-indexes-on-fields-with-embedded-arrays) - To use the partial index, a query must contain the filter expression (or a modified filter expression that specifies a subset of the filter expression) as part of its query condition. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#query-coverage) - Use a Sparse Index to Improve $exists Performance — [source](https://www.mongodb.com/docs/manual/reference/operator/query/exists/) - To create a sparse index, use the [`db.collection.createIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.createindex.md#mongodb-method-db.collection.createIndex) method with the `sparse` option set to `true`. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#create-a-sparse-index) - To obtain the correct count of the number of documents in the `movies` collection, do not [`hint()`](https://www.mongodb.com/docs/manual/reference/method/cursor.hint.md#mongodb-method-cursor.hint) with a [sparse index](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) when performing a count of all documents in a collection. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#sparse-index-and-incomplete-results) - To use the sparse index, explicitly specify the index with [`hint()`:](https://www.mongodb.com/docs/manual/reference/method/cursor.hint.md#mongodb-method-cursor.hint) — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#sparse-index-on-a-collection-cannot-return-complete-results) - To hide an index, you must have [featureCompatibilityVersion](https://www.mongodb.com/docs/manual/reference/command/setfeaturecompatibilityversion.md#std-label-view-fcv) set to `7.0` or greater. — [source](https://www.mongodb.com/docs/manual/reference/command/collMod.md#hide-an-index-from-the-query-planner) - To analyze query performance, see [Interpret Explain Plan Results.](https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan.md) — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#create-indexes-to-support-queries) - To improve query performance, build indexes on fields that appear often in queries and for all operations that [sort by a field.](https://www.mongodb.com/docs/compass/query/sort.md#std-label-compass-query-bar-sort) — [source](https://www.mongodb.com/docs/compass/indexes.md) - Create Selective Indexes to Answer Queries Efficiently — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - To learn more about the impact of unnecessary indexes, see [Remove Unnecessary Indexes.](https://www.mongodb.com/docs/manual/data-modeling/design-antipatterns/unnecessary-indexes/) — [source](https://www.mongodb.com/docs/atlas/performance-advisor/drop-indexes.md) - Add one or more indexes to better serve the inefficient queries. — [source](https://www.mongodb.com/docs/atlas/reference/alert-resolutions/query-targeting.md#fix-the-immediate-problem) - To match scalar values, use the equality operator along with the scalar value that you want to match (`{$eq: }`). For example, the following [`find()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.find.md#mongodb-method-db.collection.find) operation queries for all documents where the value of the `zipcode` field is `"63109"`. The projection includes the matching element of the `athletics` array where the value is `basketball`: — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#elemmatch-https-www-mongodb-com-docs-manual-reference-operator-projection-elemmatch-md-mongodb-projection-proj-elemmatch-with-multiple-fields) - To avoid an in-memory sort, place the range filter after the sort predicate. For more information on in-memory sorts, see cursor.allowDiskUse(). — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - To avoid an in-memory sort, place the range filter after the sort predicate. For more information on in-memory sorts, see [`cursor.allowDiskUse()`.](https://www.mongodb.com/docs/manual/reference/method/cursor.allowdiskuse.md#mongodb-method-cursor.allowDiskUse) — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline.md#range) - To specify criteria on multiple fields of documents inside that array, use the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) query operator. The following query returns the first document inside a `grades` array that has a `mean` of greater than 70 and a `grade` of greater than 90. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/positional.md#array-field-limitations) - To perform an `$elemMatch` projection when using the .NET/C# driver, call the `ElemMatch()` method on the projection builder. Pass the name of the array field to project and the filter to apply to the array elements. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#zipcode-search) - To perform an `$elemMatch` operation against scalar values in an array when using the .NET/C# driver, call the `ElemMatch()` method on the projection builder. Pass the name of the array field to project and an equality filter for the field `"$eq"` and the value you want to compare against. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#elemmatch-https-www-mongodb-com-docs-manual-reference-operator-projection-elemmatch-md-mongodb-projection-proj-elemmatch-with-multiple-fields) - To find the distinct values for a sharded collection, use the aggregation pipeline with the [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) stage instead. See [Distinct Operation](https://www.mongodb.com/docs/manual/core/transactions-operations.md#std-label-transactions-operations-distinct) for details. — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#transactions) - To find the distinct values for a sharded collection, use the aggregation pipeline with the $group stage instead. See Distinct Operation for details. — [source](https://www.mongodb.com/docs/manual/reference/command/distinct/) - To filter documents based on elements in an array field, use the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) operator in the query predicate of the `$match` stage: — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#match-array-elements) - Use the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) operator to specify multiple criteria on array elements so that at least one array element satisfies all the specified criteria. — [source](https://www.mongodb.com/docs/manual/tutorial/query-arrays.md#query-for-an-array-element-that-meets-multiple-criteria) ## Examples and snippets - ```javascript — `db.users.createIndex(` … — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#comparison-with-sparse-indexes) - ````text — `## Summary` … — [source](https://www.mongodb.com/docs/compass/query-with-natural-language/compass-ai-assistant.md#explain-plan) - [ — `{ "$match": { "borough": "Manhattan", "cuisine": "Italian" } },` … — [source](https://www.mongodb.com/docs/compass/query-with-natural-language/compass-ai-assistant.md#explain-plan) - ```javascript — `db.runCommand( {` … — [source](https://www.mongodb.com/docs/manual/reference/command/collMod.md#change-index-properties) - ```javascript — `db.runCommand( {` … — [source](https://www.mongodb.com/docs/manual/reference/command/collMod.md#hide-an-index-from-the-query-planner) - ```text — `# Create a compound index named property_room_bedrooms on the` … — [source](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-clusters-indexes-create.md#examples) - ```javascript — `db.people.find(` … — [source](https://www.mongodb.com/docs/manual/tutorial/measure-index-use.md#control-index-use-with-hint) - ```javascript — `db.people.explain("executionStats").find(` … — [source](https://www.mongodb.com/docs/manual/tutorial/measure-index-use.md#control-index-use-with-hint) - ```text — `"explainVersion": "1",` … — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#last-point-queries-on-time-series-collections) - ```java — `var explainCommand = new Document("explain", new Document()` … — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#use-secondary-indexes-to-improve-sort-performance) - ```javascript — `db.sensorData.explain( "executionStats" ).aggregate( [` … — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#use-secondary-indexes-to-improve-sort-performance) - ```javascript — `db.getCollection( 'sensorData' ).explain( "executionStats" ).aggregate( [` … — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#last-point-queries-on-time-series-collections) - ```javascript — `const explainResult = await database.command({` … — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#use-secondary-indexes-to-improve-sort-performance) - ```python — `explain_result = database.command(` … — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#use-secondary-indexes-to-improve-sort-performance) - ```javascript — `queryPlanner: {` … — [source](https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan.md#mongodb-shell) - ```javascript — `_id: 'TV-PG'` … — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#retrieve-distinct-values) - ```csharp — `var results = schoolsCollection` … — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#elemmatch-https-www-mongodb-com-docs-manual-reference-operator-projection-elemmatch-md-mongodb-projection-proj-elemmatch-with-multiple-fields) - ```javascript — `_id: ObjectId(""),` … — [source](https://www.mongodb.com/docs/manual/reference/operator/query/all.md#use-all-with-elemmatch) - ```javascript — `db.movies.createIndex( { year: 1 } )` … — [source](https://www.mongodb.com/docs/manual/core/query-plans.md#plancachekey) - ```shell — `"collation": {` … — [source](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-api-rollingIndex-createRollingIndex.md#example) - ```javascript — `db.users.createIndex(` … — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#query-coverage) - ```javascript — `input: ,` … — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sortArray.md#sort-by-document-fields) - ```javascript — `input: ,` … — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sortArray.md#sort-by-value) - ```javascript — `db.collection.createIndex(` … — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex.md#options) - ```javascript — `db.orders.createIndex( { item: 1, quantity: 1 } )` … — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats.md#mongodb-shell) - ```javascript — `name: 'type_1_item_1',` … — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats.md#mongodb-shell) - ```javascript — `db.events.createIndex(` … — [source](https://www.mongodb.com/docs/manual/tutorial/expire-data.md#expire-documents-with-filter-conditions) - ```javascript — `{ : { $elemMatch: { , , ... } } }` … — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#syntax) - ```javascript — `db.scores.find(` … — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#element-match) - ```javascript — `db.survey.find(` … — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#example-1) - ```javascript — `db.survey.find(` … — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#example-2) - ```javascript — `db.players.find( {}, { games: { $elemMatch: { score: { $gt: 5 } } }, joined: 1, lastLogin: 1 } )` … — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#field-order) - ```javascript — `db.schools.find( { zipcode: "63109" },` … — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#zipcode-search) - ```csharp — `var results = schoolsCollection` … — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#zipcode-search) - ```javascript — `db.schools.find( { zipcode: "63109" },` … — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#elemmatch-https-www-mongodb-com-docs-manual-reference-operator-projection-elemmatch-md-mongodb-projection-proj-elemmatch-with-multiple-fields) - ```javascript — `db.aggregate( [` … — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#match-array-elements) - ```javascript — `db.movies.find(` … — [source](https://www.mongodb.com/docs/manual/tutorial/query-arrays.md#query-for-an-array-element-that-meets-multiple-criteria) - ```python — `cursor = db.inventory.find({"dim_cm": {"$elemMatch": {"$gt": 22, "$lt": 30}}})` … — [source](https://www.mongodb.com/docs/manual/tutorial/query-arrays.md#query-for-an-array-element-that-meets-multiple-criteria) - ```javascript — `const cursor = db.collection('inventory').find({` … — [source](https://www.mongodb.com/docs/manual/tutorial/query-arrays.md#query-for-an-array-element-that-meets-multiple-criteria) - ```php — `$cursor = $db->inventory->find([` … — [source](https://www.mongodb.com/docs/manual/tutorial/query-arrays.md#query-for-an-array-element-that-meets-multiple-criteria) - ```ruby — `client[:inventory].find(dim_cm: { '$elemMatch' => { '$gt' => 22,` … — [source](https://www.mongodb.com/docs/manual/tutorial/query-arrays.md#query-for-an-array-element-that-meets-multiple-criteria) - ```scala — `findObservable = collection.find(elemMatch("dim_cm", Document("$gt" -> 22, "$lt" -> 30)))` … — [source](https://www.mongodb.com/docs/manual/tutorial/query-arrays.md#query-for-an-array-element-that-meets-multiple-criteria) - ```javascript — `{ dim_cm: { $elemMatch: { $gt: 22, $lt: 30 } } }` … — [source](https://www.mongodb.com/docs/manual/tutorial/query-arrays.md#query-for-an-array-element-that-meets-multiple-criteria) - ```text — `{ languages: { $elemMatch: { $ne: null, $ne: "English" } } }` … — [source](https://www.mongodb.com/docs/manual/tutorial/query-arrays.md#query-for-an-array-element-that-meets-multiple-criteria) - ```javascript — `db.inventory.find( {` … — [source](https://www.mongodb.com/docs/manual/reference/operator/query/all.md#use-all-with-elemmatch) - ```ruby — `client[:inventory].find(instock: { '$elemMatch' => { qty: 5,` … — [source](https://www.mongodb.com/docs/manual/tutorial/query-array-of-documents.md#a-single-nested-document-meets-multiple-query-conditions-on-nested-fields) - ```ruby — `client[:inventory].find(instock: { '$elemMatch' => { qty: { '$gt' => 10,` … — [source](https://www.mongodb.com/docs/manual/tutorial/query-array-of-documents.md#a-single-nested-document-meets-multiple-query-conditions-on-nested-fields) - ```javascript — `{ "instock": { $elemMatch: { qty: 5, warehouse: "A" } } }` … — [source](https://www.mongodb.com/docs/manual/tutorial/query-array-of-documents.md#a-single-nested-document-meets-multiple-query-conditions-on-nested-fields) - ```javascript — `{ "instock": { $elemMatch: { qty: { $gt: 10, $lte: 20 } } } }` … — [source](https://www.mongodb.com/docs/manual/tutorial/query-array-of-documents.md#a-single-nested-document-meets-multiple-query-conditions-on-nested-fields) - ```shell — `{ scores: { $elemMatch: { $gt: 80, $lt: 90 } } }` … — [source](https://www.mongodb.com/docs/atlas/atlas-ui/query/filter.md#match-by-array-conditions) ## Measurements and reference values - Covered indexes cost size. In a forum case a fully covering index was about 8x the single-field index and ran slightly slower than it for a name-only query. Unanchored regex scans the whole index keyspace; an anchored prefix regex bounds the scan and is much faster. — [source](https://mongodb.com/community/forums/t/adding-the-projected-fields-to-compound-index-to-create-covered-query-doesnt-improve-performance/199267) - Real-world symptom: on MongoDB 4.4.14 a range query on a multikey path (products.productInfo.productId) with $gt and $lte used only the (479894, inf] bound, with the $lte as a FETCH filter. It examined 741,071 keys and 399,367 docs and took 10-14 s. Wrapping both predicates in $elemMatch fixed it. — [source](https://www.mongodb.com/community/forums/t/mongo-indexbounds-doubts/165994) - For any configuration of granularity parameters, the maximum size of a bucket is 1000 measurements or 125KB of data, whichever is lower. MongoDB may also enforce a lower maximum size for high cardinality data with many unique values, so that the working set of buckets fits within the [WiredTiger cache.](https://www.mongodb.com/docs/manual/core/wiredtiger.md#std-label-storage-wiredtiger) — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-limitations.md#bucket-size) ## Problems, failure modes and limitations - Multikey indexes can cover queries over non-array fields only if the index tracks which fields cause it to be multikey. They cannot cover queries over array fields. In one forum case an index including an array field forced a FETCH and examined about 5.1M documents; the same index without that field examined none. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - Diagnostic workflow: run explain('executionStats') or explain('allPlansExecution') and look for AND_SORTED/AND_HASH in the winning or rejected plans; use $indexStats to compare per-index usage. Intersection is a fallback when a compound index cannot be added. — [source](https://oneuptime.com/blog/post/2026-03-31-mongodb-how-to-use-index-intersection-in-mongodb/markdown) - If your query specifies `$elemMatch` on fields that diverge from a common path, MongoDB **cannot** compound the bounds of index keys from the same array. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-elemmatch-on-diverging-field-paths) - MongoDB cannot compound the index bounds and the `"ratings.scores.q2"` field is unconstrained during the index scan. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-elemmatch-on-diverging-field-paths) - With .sort(), an $in list of fewer than 201 elements is expanded and merged in index sort order with a SORT_MERGE stage, so it acts like an equality field. At 201 or more elements it is ordered like a range, and later index fields cannot be used for sorting. MongoDB says this threshold is not guaranteed to stay the same across versions. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - If the query does not join the conditions on the array field with `$elemMatch`, MongoDB cannot intersect the multikey index bounds. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#query-without-elemmatch) - [Multikey indexes](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multikey) cannot cover queries over array fields. — [source](https://www.mongodb.com/docs/manual/reference/limits.md#indexes) - If MongoDB cannot compound the two bounds, MongoDB constrains the index scan by the bound on the leading field. In this example, the leading field is `temperature`, resulting in a constraint of `temperature: [ [ 80, Infinity ] ]`. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#compound-bounds-for-a-multikey-index) - However, the following query cannot use the partial index on the `name` field because using the index results in an incomplete result set. Specifically, the query predicate includes the condition `password: { $exists: false }` while the index has the filter `password: { $exists: true }`. That is, the query `{ name: "Ned Stark", password: { $exists: false } }` matches more documents (users without passwords) than the index covers. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#query-coverage) - MongoDB docs recommend a partial index over a sparse index for precise control. A partial index with partialFilterExpression {field:{$exists:true}} reproduces sparse behavior, and a single index cannot specify both partialFilterExpression and sparse. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md) - From MongoDB 9.0, a query on an indexed field that becomes multikey while the query runs may fail with QueryKilledError; re-run it after the insert or update finishes. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md) - When a query specifies an exact match for an array as a whole, MongoDB uses the multikey index to locate documents that contain the first element of that array. However, it cannot match the entire array using the index alone. MongoDB then fetches the candidate documents and filters them to return only those whose array exactly matches the query array. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - The above unsupported index types are ignored by the query planner in strict mode. For example, attempting to use a sparse index with cursor.hint() results in the following BadValue error: — [source](https://www.mongodb.com/docs/manual/reference/command/createIndexes/) - - You cannot specify both the `partialFilterExpression` option and the `sparse` option. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#restrictions) - Starting in MongoDB 7.3, you cannot create equivalent indexes, which are partial indexes with the same index keys and the same partial expressions that use a [collation.](https://www.mongodb.com/docs/manual/reference/collation.md#std-label-collation) — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#equivalent-indexes) - - The above unsupported index types are ignored by the [query planner](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-query-plans-query-optimization) in [strict mode](https://www.mongodb.com/docs/manual/reference/stable-api.md#std-label-stable-api-strict-client). For example, attempting to use a `sparse` index with [`cursor.hint()`](https://www.mongodb.com/docs/manual/reference/method/cursor.hint.md#mongodb-method-cursor.hint) results in the following `BadValue` error: — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndexes.md#stable-api) - | `$exists` Query | Using a Sparse Index | Using a Non-Sparse Index | | --- | --- | --- | | `{ $exists: true }` | Most efficient. MongoDB can make an exact match and does not require a `FETCH`. | More efficient than queries without an index, but still requires a `FETCH`. | | `{ $exists: false }` | Cannot use the index and requires a `COLLSCAN`. | Requires a `FETCH`. | — [source](https://www.mongodb.com/docs/manual/reference/operator/query/exists.md#use-a-sparse-index-to-improve-exists-performance) - | Field | Description | | --- | --- | | [into](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md#std-label-merge-into) | The output collection. Specify either: The collection name as a string to output to a collection in the same database where the aggregation is run. For example:`into: "myOutput"`; The database and collection name in a document to output to a collection in the specified database. For example:`into: { db:"myDB", coll:"myOutput" }` If the output collection does not exist, `$merge` creates the collection: For a [replica set](https://www.mongodb.com/docs — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md#syntax) - If MongoDB cannot use an index or indexes to obtain the sort order, the results include a `SORT` stage indicating an in-memory sort operation. If the explain plan does not contain an explicit `SORT` stage, then MongoDB used an index to obtain the sort order. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#sort-stage) - A compound multikey index allows at most one array-valued field per document. Creating the index fails if documents have arrays in more than one indexed field, and later inserts that violate this fail. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md) - From MongoDB 7.3, equivalent indexes (same keys and same partial expression with a collation) cannot be created; pre-existing ones in upgraded databases are kept but only the first is used. Since 5.0, multiple partial indexes with the same key pattern are allowed if filters are not equivalent. — [source](https://mongodb.com/docs/v7.3/core/index-partial) - | Field | Type | Literal or Normalized | Description | | --- | --- | --- | --- | | `key` | Document | Literal | Contains the query shape and additional query attributes that group a set of queries together | | `key.queryShape` | Document | Literal | Contains attributes used to group similar queries together. For more information, see [Query Shape.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querystats.md#std-label-queryStats-queryShape) | | `key.client` | Document | Literal | Describes client information associated with the key | | `key.client.application` | Document | — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats.md#output) - | Field | Type | Description | | --- | --- | --- | | `distinct` | string | The name of the collection to query for distinct values. | | `key` | string | The field for which to return distinct values. | | `query` | document | Optional. A query that specifies the documents from which to retrieve the distinct values. | | `readConcern` | document | Optional. Specifies the [read concern.](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-read-concern) The `readConcern` option has the following syntax: `readConcern: { level: }` Possible read concern levels are: [`"local"`](h — [source](https://www.mongodb.com/docs/manual/reference/command/distinct.md#command-fields) - If a document has a `null` or missing value for the indexed field in a unique single-field index, the index stores a `null` value for that document. Because of the unique constraint, a single-field unique index can only contain one document that contains a `null` value in its index entry. If there is more than one document with a `null` value in its index entry, the index build fails with a duplicate key error. — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#missing-document-field-in-a-unique-single-field-index) - However, the following query cannot use the index because it includes a null match on the `email` field, which is not permitted by the filter expression `{ email: { $exists: true } }`: — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#comparison-with-sparse-indexes) - Starting in MongoDB 9.0, if an indexed field becomes a multikey field while a query that references the field is running, the query might fail with a QueryKilledError. An indexed field becomes multikey when you insert or update a document so the field contains an array value. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - | Method | Availability | Description | | --- | --- | --- | | Use the Atlas Query Profiler | M10+ Atlas clusters | The Atlas Query Profiler shows long-running operations and performance statistics. For more information, see [Monitor Query Performance with the Query Profiler.](https://www.mongodb.com/docs/atlas/tutorial/query-profiler.md#std-label-profile-database) | | Enable the Database Profiler | Atlas clusters and self-hosted deployments | When enabled, the database profiler stores information about slow queries in the [`system.profile`](https://www.mongodb.com/docs/manual/reference/system- — [source](https://www.mongodb.com/docs/manual/tutorial/evaluate-operation-performance.md#analyze-a-slow-query) - Compound indexes cannot support queries where the sort order does not match the index or the reverse of the index. As a result, the `{ score: -1, username: 1 }` index **cannot** support sorting by ascending `score` values and then by ascending `username` values, such as this query: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/sort-order.md#unsupported-queries) - Hiding or unhiding an index resets its $indexStats counters, so record usage counts before toggling. Hiding needs featureCompatibilityVersion 7.0 or higher; the _id index cannot be hidden; cursor.hint() cannot target a hidden index. — [source](https://www.mongodb.com/docs/upcoming/core/index-hidden/) - However, you cannot insert a second document without the `email` field if the collection already contains a document missing the `email` field. A second operation that attempts to insert another document without the `email` field fails to insert the document because of the violation of the unique constraint on `email` field. — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#missing-document-field-in-a-unique-single-field-index) - You cannot create a compound multikey index if more than one field in the index specification is an array. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - If a compound multikey index already exists, you cannot insert a document that would violate this restriction. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - You cannot specify a multikey index as a shard key index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - If MongoDB cannot use an index or indexes to obtain the sort order, MongoDB must perform an in-memory sort operation on the data. — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.sort.md#sort-and-index-use) - Similarly, the following query cannot use the partial index because the query predicate does not include the filter expression and using the index would return an incomplete result set. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#query-coverage) - - `_id` indexes cannot be partial indexes. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#restrictions) - - Shard key indexes cannot be partial indexes. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#restrictions) - For example, you can't create two indexes that only differ in the text case in the partial filter expression. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#equivalent-indexes) - However, the following query cannot use the partial index because the query predicate does not include the `rating` filter: — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#create-a-partial-index-on-a-collection) - Since reading.timestamp is not included in an index, MongoDB must perform an in-memory sort operation to return results in the requested sort order. By specifying cursor.allowDiskUse(false), MongoDB cannot process the sort operation if it requires more than 100 megabytes of system memory. If the operation requires more than 100 megabytes of system memory, MongoDB would return an error. — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.allowDiskUse/) - You cannot create [partial indexes](https://www.mongodb.com/docs/manual/core/index-partial.md#std-label-index-type-partial) on the metaField and timeField. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-index.md#partial-indexes) - You cannot create sparse indexes on the metaField. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-index.md#prohibited-indexes) - Multikey indexes cannot cover queries over array fields. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - If a sparse index would result in an incomplete result set for queries and sort operations, MongoDB will not use that index unless a [`hint()`](https://www.mongodb.com/docs/manual/reference/method/cursor.hint.md#mongodb-method-cursor.hint) explicitly specifies the index. See [Sparse Index On A Collection Cannot Return Complete Results](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-sparse-index-incomplete-results) for an example. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#sparse-index-and-incomplete-results) - In a compound multikey index, MongoDB compounds bounds across scalar-field keys and array-field keys. Example: {item: 1, ratings: 1} with item: 'XYZ', ratings: {$gte: 3} yields both bounds. If bounds cannot be compounded, MongoDB constrains the scan by the leading field only. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md) - For example, a comparison between `$a` and a constant value can use an index, but a comparison between `$a` and `$b` cannot. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/expr.md#expr-in-lookup-operations) - "Range" filters scan fields. The scan doesn't require an exact match, which means range filters are loosely bound to index keys. To improve query efficiency, limit the range bounds and use equality matches to reduce the number of documents to scan. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - Additionally, for a sort operation to use an index, the fields specified in the sort must appear in the same order that they appear in an index. As a result, the above index cannot support this query: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/sort-order.md#unsupported-queries) - - For an already-sharded collection, you cannot create unique indexes on other fields unless the shard key is included as the prefix. — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#sharded-clusters-and-unique-indexes) - Without the index, the query scans the entire collection to return the matching documents. The query also has to scan the entirety of each document, potentially pulling them into memory. This results in an expensive and potentially slow query operation. — [source](https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan.md#mongodb-shell) - For [time series collections](https://www.mongodb.com/docs/manual/core/timeseries-collections.md#std-label-manual-timeseries-landing), the `distinct` command can't make efficient use of indexes. Instead, use a [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) aggregation to group documents by distinct values. For details, see [Time Series Limitations.](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-limitations.md#std-label-timeseries-limitation-distinct) — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#behavior) - | Method | Availability | Description | | --- | --- | --- | | Use the Atlas Performance Advisor | M10+ Atlas clusters | The Atlas Performance Advisor monitors slow queries and suggests new indexes to improve performance. For more information, see [Monitor and Improve Slow Queries with the Performance Advisor.](https://www.mongodb.com/docs/atlas/performance-advisor.md#std-label-performance-advisor) | | Check ongoing operations in Atlas | M10+ Atlas clusters | You can use the [Atlas Real-Time Performance Panel](https://www.mongodb.com/docs/atlas/real-time-performance-panel.md#std-label-real-time — [source](https://www.mongodb.com/docs/manual/tutorial/evaluate-operation-performance.md#performance-overview) - For time series collections, the distinct command can't make efficient use of indexes. Instead, use a $group aggregation to group documents by distinct values. For details, see Time Series Limitations. — [source](https://www.mongodb.com/docs/manual/reference/command/distinct/) - [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch), [`$slice`](https://www.mongodb.com/docs/manual/reference/operator/projection/slice.md#mongodb-projection-proj.-slice), and [`$`](https://www.mongodb.com/docs/manual/reference/operator/projection/positional.md#mongodb-projection-proj.-) are the *only* operators that you can use to project specific elements to include in the returned array. For instance, you *cannot* project specific array elements using the array index; e.g. `{ "instock.0": 1 }` projection does *not — [source](https://www.mongodb.com/docs/manual/tutorial/project-fields-from-query-results.md#project-specific-array-elements-in-the-returned-array) - [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch), [`$slice`](https://www.mongodb.com/docs/manual/reference/operator/projection/slice.md#mongodb-projection-proj.-slice), and [`$`](https://www.mongodb.com/docs/manual/reference/operator/projection/positional.md#mongodb-projection-proj.-) are the *only* operators that you can use to project specific elements to include in the returned array. For instance, you *cannot* project specific array elements using the array index; e.g. `include("instock.0")` projection does *n — [source](https://www.mongodb.com/docs/manual/tutorial/project-fields-from-query-results.md#project-specific-array-elements-in-the-returned-array) - [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch), [`$slice`](https://www.mongodb.com/docs/manual/reference/operator/projection/slice.md#mongodb-projection-proj.-slice), and [`$`](https://www.mongodb.com/docs/manual/reference/operator/projection/positional.md#mongodb-projection-proj.-) are the *only* operators that you can use to project specific elements to include in the returned array. For instance, you *cannot* project specific array elements using the array index; e.g. `{ "instock.0" => 1 }` projection does *n — [source](https://www.mongodb.com/docs/manual/tutorial/project-fields-from-query-results.md#project-specific-array-elements-in-the-returned-array) - [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch), [`$slice`](https://www.mongodb.com/docs/manual/reference/operator/projection/slice.md#mongodb-projection-proj.-slice), and [`$`](https://www.mongodb.com/docs/manual/reference/operator/projection/positional.md#mongodb-projection-proj.-) are the only operators that you can use to project specific elements to include in the returned array. For instance, you cannot project specific array elements using the array index; e.g. `{ "instock.0": 1 }` projection does not proj — [source](https://www.mongodb.com/docs/manual/tutorial/project-fields-from-query-results.md#project-specific-array-elements-in-the-returned-array) - If you are using Client-Side Field Level Encryption or Queryable Encryption, a partialFilterExpression cannot reference an encrypted field. — [source](https://www.mongodb.com/docs/manual/reference/command/createIndexes/) - - If you are using [Client-Side Field Level Encryption](https://www.mongodb.com/docs/manual/core/csfle.md#std-label-manual-csfle-feature) or [Queryable Encryption](https://www.mongodb.com/docs/manual/core/queryable-encryption.md#std-label-qe-manual-feature-qe), a `partialFilterExpression` cannot reference an encrypted field. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#restrictions) - After creating a time series collection, you can modify its granularity using the [`collMod`](https://www.mongodb.com/docs/manual/reference/command/collmod.md#mongodb-dbcommand-dbcmd.collMod) method. However, you can only increase the timespan covered by each bucket. You cannot decrease it. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/out.md#syntax) - The view's underlying aggregation pipeline is subject to the 100 megabyte memory limit for blocking sort and blocking group operations. — [source](https://www.mongodb.com/docs/manual/core/views.md#view-pipelines) - The number of non-retriable change stream errors not covered by another counter under `metrics.changeStreams.error.nonRetriable`. — [source](https://www.mongodb.com/docs/manual/reference/command/serverStatus.md#metrics) - The number of retriable change stream errors not covered by another `metrics.changeStreams.error.retriable` counter. — [source](https://www.mongodb.com/docs/manual/reference/command/serverStatus.md#metrics) - The [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) projection operator takes an explicit condition argument. This allows you to project based on a condition not in the query, or if you need to project based on multiple fields in the array's embedded documents. See [Array Field Limitations](https://www.mongodb.com/docs/manual/reference/operator/projection/positional.md#std-label-array-field-limitation) for an example. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/positional.md#usage-considerations) - - You cannot specify a [`$where`](https://www.mongodb.com/docs/manual/reference/operator/query/where.md#mongodb-query-op.-where) operator in an [`$elemMatch`.](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#behavior) - - You cannot specify a [`$text`](https://www.mongodb.com/docs/manual/reference/operator/query/text.md#mongodb-query-op.-text) query operator in an [`$elemMatch`.](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#behavior) - - `$text` cannot appear in [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) query or projection expressions. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/text.md#restrictions) - Results must not be larger than the maximum [BSON size](https://www.mongodb.com/docs/manual/reference/limits.md#std-label-limit-bson-document-size). If your results exceed the maximum BSON size, use the aggregation pipeline to retrieve distinct values using the [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) operator, as described in [Retrieve Distinct Values with the Aggregation Pipeline.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#std-label-aggregation-group-distinct-values) — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#syntax) ## Comparisons and alternatives - Placing sort fields before range fields lets the index return results in index order and avoids a blocking in-memory SORT stage. The trade-off is that more index keys may be scanned. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - Alex Bevi's explain examples show the trade-off. With sort on region and range on joined, {joined:1, region:1} examined 4 keys and used an in-memory SORT stage. {region:1, joined:1} examined 5 keys with no blocking sort. He also says equality fields need not be ordered from most to least selective. — [source](https://alexbevi.com/blog/2020/05/16/optimizing-mongodb-compound-indexes-the-equality-sort-range-esr-rule/) - A single-field index whose key is the leading prefix of a compound index (e.g. {field:1} next to {field:1, other:1}) is redundant: the compound index serves the same filter. Keep the compound index and drop the prefix one. The manual's example drops {time:1} as covered by {day:1, time:1}, though that example's field names are inconsistent (days vs day). — [source](https://mongodb.com/docs/manual/data-modeling/design-antipatterns/unnecessary-indexes/) - | Field | Type | Description | | --- | --- | --- | | `find` | string | The name of the collection or [view](https://www.mongodb.com/docs/manual/core/views.md#std-label-views-landing-page) to query. | | `filter` | document | Optional. The query predicate. If unspecified, all documents in the collection match the predicate. | | [sort](https://www.mongodb.com/docs/manual/reference/command/find.md#std-label-find-cmd-sort) | document | Optional. The sort specification for the ordering of the results. | | `projection` | document | Optional. The [projection specification](https://www.mongodb.com/docs — [source](https://www.mongodb.com/docs/manual/reference/command/find.md#command-fields) - | Counter | Description | | --- | --- | | `collscanNoFilter``collscanWithFilter` | Collection scan stages, counted separately based on whether the stage applies a filter. | | `ixscanNoFilter``ixscanWithFilter` | Index scan stages, counted separately based on whether the stage applies a filter. | | `fetchNoFilter``fetchWithFilter` | Fetch stages, counted separately based on whether the stage applies a filter. | | `andHashNoFilter``andHashWithFilter` | Hash-based index intersection stages, counted separately based on whether the stage applies a filter. | | `andSorted` | Sorted index intersection — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats.md#node-counters) - Use partial indexes instead of [sparse indexes](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) for more precise control over which documents to index: — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#comparison-with-sparse-indexes) - The second compound index, `{ rated: 1, year: 1 }`, is therefore the more efficient index for supporting the example query, as the MongoDB server only needs to scan `235` [`index keys`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.totalKeysExamined) to find all matching documents using this index, compared to `246` when using the compound index `{ year: 1, rated: 1 }`. — [source](https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan.md#mongodb-shell) - Sparse indexes in MongoDB differ from [block-level](http://en.wikipedia.org/wiki/Database_index#Sparse_index) sparse indexes in other databases. Sparse indexes only contain entries for documents that have the indexed field, even if the index field contains a null value. The index skips over any document that is missing the indexed field. The index is "sparse" because it does not include all documents of a collection. By contrast, non-sparse indexes contain all documents in a collection, storing null values for those documents that do not contain the indexed field. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md) - Since MongoDB 3.3.8 (SERVER-15086), the server tracks multikey status per path rather than one flag per index. This lets the planner apply both bounds on a scalar leading field of a compound multikey index. Before that, it could bound only one side and filtered the rest after FETCH. explain now shows multiKeyPaths on IXSCAN. The change covers WiredTiger and in-memory engines. — [source](https://jira.mongodb.org/browse/SERVER-15086) - | Field | Field Type | Necessity | Description | | --- | --- | --- | --- | | `setQuerySettings` | document or string | Required | You can provide either: The same fields as those in a [`find`](https://www.mongodb.com/docs/manual/reference/command/find.md#mongodb-dbcommand-dbcmd.find), [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct), or [`aggregate`](https://www.mongodb.com/docs/manual/reference/command/aggregate.md#mongodb-dbcommand-dbcmd.aggregate) command, and a `$db` field with the database associated with the original command — [source](https://www.mongodb.com/docs/manual/reference/command/setQuerySettings.md#command-fields) - To support this query, create one index on `runtime` and another index on `year`, rather than a compound index: — [source](https://www.mongodb.com/docs/manual/reference/operator/query/or.md#or-clauses-and-indexes) - Compared to normal collections, storing time series data in time series collections improves query efficiency and reduces the disk usage for time series data and [secondary indexes](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-secondary-index). MongoDB 6.3 and later automatically creates a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound) on the time and metadata fields for new time series collections. — [source](https://www.mongodb.com/docs/manual/core/timeseries-collections.md#benefits) - | Option | Description | | --- | --- | | allowDiskUse | Whether or not pipelines that require more than 100 megabytes of memory to execute write to temporary files on disk. For details, see [`cursor.allowDiskUse()`.](https://www.mongodb.com/docs/manual/reference/method/cursor.allowdiskuse.md#mongodb-method-cursor.allowDiskUse) | | allowPartialResults | For queries against a sharded collection, allows the command (or subsequent getMore commands) to return partial results, rather than an error, if one or more queried shards are unavailable. | | awaitData | If the cursor is a a tailable-await cur — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.find.md#options) - Given these queries, the index with the [partial filter expression](https://www.mongodb.com/docs/manual/core/index-partial.md#std-label-partial-index-query-coverage) can support query operation 2 but *not* support query operation 1. Since the indexes available to support query operation 1 differs from query operation 2, the two queries have different `planCacheKey`. — [source](https://www.mongodb.com/docs/manual/core/query-plans.md#plancachekey) - You can have a clustered index and add [secondary indexes](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-secondary-index) to a clustered collection. Clustered indexes differ from secondary indexes: — [source](https://www.mongodb.com/docs/manual/core/clustered-collections.md#behavior) - Storing the collection's documents in index order can provide benefits for storage and performance compared to traditional collections and their related regular indexes. — [source](https://www.mongodb.com/docs/manual/core/clustered-collections.md) - Indexes improve read performance, but a large number of indexes can negatively impact write performance since indexes must be updated during writes. If your collection already has several indexes, consider this tradeoff of read and write performance when deciding whether to create new indexes. Examine whether a query for such a collection can be modified to take advantage of existing indexes, as well as whether a query occurs often enough to justify the cost of a new index. — [source](https://www.mongodb.com/docs/atlas/performance-advisor.md#index-considerations) - The argument to [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) matches elements of the array that `$elemMatch` is projecting. If you specify an equality with a field name to `$elemMatch`, it attempts to match objects within the array. For example, `$elemMatch` attempts to match objects, instead of scalar values, within the array for the following in the projection: — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#elemmatch-https-www-mongodb-com-docs-manual-reference-operator-projection-elemmatch-md-mongodb-projection-proj-elemmatch-with-multiple-fields) ## Facts and statements - If you have a collection that has both a compound index and an index on its prefix (for example, `{ a: 1, b: 1 }` and `{ a: 1 }`), if neither index has a [sparse](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) or [unique](https://www.mongodb.com/docs/manual/core/index-unique.md#std-label-index-type-unique) constraint, you can remove the index on the prefix (`{ a: 1 }`). MongoDB uses the compound index in all of the situations that it would have used the prefix index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#index-prefixes) - MongoDB's current manual presents ESR as a guideline with an explicit exception. If the range predicate is very selective, putting it before the sort fields (ERS) reduces the number of documents sorted, and the query accepts an in-memory sort. If avoiding in-memory sorts is critical, use ESR. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - Community report (Jan 2022, no replies captured): on a ~1.2M-document collection, a sparse compound index {A,B,C} serving { A:{$exists:true}, B:false, C:{$lt:date} } logged about 575,000 keys examined and almost 300,000 documents examined. The same keys as a partial index with filter {A:{$exists:true}} gave much better targeting. This is consistent with, but not proof of, the sparse compound rule that documents lacking A are still indexed. — [source](https://www.mongodb.com/community/forums/t/sparse-vs-partial-index-for-compound-index/143869) - For example, consider a compound index `{ temperature: 1, humidity: 1 }` with the following bounds: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#compound-bounds-for-a-multikey-index) - Index intersection does not help a sort that needs an index separate from the query predicate; indexed sorts require a compound index whose key order matches the filter and sort. — [source](https://web.archive.org/web/2024/https://www.mongodb.com/docs/v6.0/core/index-intersection/) - For a compound index that includes a 2d index key along with keys of other types, only the 2d index field determines whether the index references a document. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2d.md#sparse-property) - The ESR guideline orders compound index keys as Equality, then Sort, then Range. Equality fields always come first, and any number of equality keys may appear in any order among themselves. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - The ESR documentation says equality fields come first and a more selective equality makes the index more efficient. If the range predicate is very selective, place it before the sort fields (ERS), accepting a possible in-memory sort. $in arrays under 201 elements act like equality; 201 or more act like range. — [source](https://mongodb.com/docs/v7.0/tutorial/equality-sort-range-rule) - Compound indexes support sort operations that match either the sort order of the index, or the reverse sort order of the index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/sort-order.md) - $elemMatch on the wrong path defeats compounding. For index {ratings.scores.q1: 1, ratings.scores.q2: 1}, ratings: {$elemMatch: {'scores.q1': 2, 'scores.q2': 8}} leaves ratings.scores.q2 unconstrained. Use {'ratings.scores': {$elemMatch: {q1: 2, q2: 8}}} instead. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md) - - Partial indexes include or exclude documents based on the filter expression. The expression can include fields other than index keys, and can specify conditions other than a field existing. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#comparison-with-sparse-indexes) - However, a partial index can also filter on fields other than the index key. For example, a partial index on the `name` field can check for the existence of the `email` field: — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#comparison-with-sparse-indexes) - The filter may reference fields not in the index keys. Index {name:1} with filter {email:{$exists:true}} serves only queries that also constrain name and require non-null email; {name:1} with {name:{$exists:true}} behaves like a sparse index. — [source](https://mongodb.com/docs/v7.3/core/index-partial) - In a [compound](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound) multikey index, each indexed document can have *at most* one indexed field whose value is an array. Specifically: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#compound-multikey-indexes) - For a query to use a compound index for a sort, the specified sort direction for all keys in the cursor.sort() document must match the index key pattern or match the inverse of the index key pattern. For example, an index key pattern { a: 1, b: -1 } can support a sort on { a: 1, b: -1 } and { a: -1, b: 1 } but not on { a: -1, b: -1 } or {a: 1, b: 1}. — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) - - Sparse indexes include or exclude documents *solely* based on the presence of the indexed field (or multiple fields, for sparse compound indexes). — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#comparison-with-sparse-indexes) - In most cases, applying the ESR (Equality, Sort, Range) Guideline to arrange the index keys creates a more efficient compound index. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - In most cases, applying the ESR (Equality, Sort, Range) Guideline to arrange the index keys creates a more efficient [compound index.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound) — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline.md) - MongoDB compounds the bounds for the `item` key with either the bounds for `"ratings.score"` or the bounds for `"ratings.by"`, depending upon the query predicates and the index key values. MongoDB does not guarantee which bounds it compounds with the `item` field. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-compound-bounds-of-non-array-field-and-multiple-array-fields) - - The index keys must share the same field path up to but excluding the field names. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#compound-bounds-of-multiple-fields-from-the-same-array) - For a field in an embedded document, the [dotted field name](https://www.mongodb.com/docs/manual/core/document.md#std-label-document-dot-notation), such as `"a.b.c.d"`, is the field path for `d`. To compound the bounds for index keys from the same array, the `$elemMatch` must be on the path up to *but excluding* the field name itself (meaning `"a.b.c"`). — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#compound-bounds-of-multiple-fields-from-the-same-array) - The following example shows how MongoDB combines bounds for index keys from the same array. This example uses the `survey2` collection used in the [previous example.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#std-label-index-bounds-example-non-array-multiple-array) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example) - Without $elemMatch, {grades: {$gte: 90, $lte: 99}} can be satisfied by different elements, so MongoDB uses only one of [[90, Infinity]] or [[-Infinity, 99]]. The docs say MongoDB gives no guarantee which one. The planner applies the other predicate as a FETCH filter. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md) - Index keys from the same array compound their bounds only if (a) the keys share the same path up to but excluding the field names, and (b) the query uses $elemMatch on that path. If the query predicates cover several array fields without $elemMatch, MongoDB compounds the leading scalar field with only one of them and does not guarantee which. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md) - Safer workaround for complex filters: set a boolean/flag field on write and use a simple equality partialFilterExpression; confirm with explain('executionStats') and hint(). — [source](https://www.mongodb.com/community/forums/t/partial-index-not-covering-or-query-filter/220425) - For example, the following operation creates a compound index that indexes only the documents where the `genre` field is `Drama`: — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#create-a-partial-index) - For example, create a compound index on the `data` collection: — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#sort-and-index-prefix) - Example from the MongoDB manual: for find({directors:'David Lynch', runtime:{$lt:130}}).sort({year:1}), the recommended index is {directors:1, year:1, runtime:1}. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - Given an indexed array field, consider a query that specifies multiple query predicates on the array and uses a [multikey index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multikey) to fulfill the query. MongoDB can intersect the multikey index bounds if an [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) operator joins the query predicates. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#bounds-intersection-for-a-multikey-index) - For example, the following operation creates a compound multikey index on the `movies` collection on the `genres` and `title` fields: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#covered-queries) - For the query optimizer to choose this partial index, the query predicate must include a condition on the `name` field as well as a *non-null* match on the `email` field. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#comparison-with-sparse-indexes) - MongoDB can combine the scans of two indexes (nested intersections are possible) to answer a query, using either whole indexes or index prefixes. The manual's example combines separate { qty: 1 } and { item: 1 } indexes on an orders collection. — [source](https://web.archive.org/web/2024/https://www.mongodb.com/docs/v6.0/core/index-intersection/) - Sorting on a multikey-indexed array field adds an in-memory sort stage unless the bounds for all sort fields are [MinKey, MaxKey] and no multikey field bound shares the sort pattern's path prefix. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md) - Common patterns: index only active/pending status subsets, soft-delete (deletedAt:null), high-value items, conditional uniqueness, and TTL limited to a subset; benefit is smaller index and less write cost for non-matching documents. — [source](https://mongodb.com/docs/v7.3/core/index-partial) - - To learn how to create efficient compound indexes, see [The ESR (Equality, Sort, Range) Guideline.](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline.md#std-label-esr-indexing-guideline) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/create-compound-index.md#learn-more) - Official guidance: schema designs should not rely on index intersection; compound indexes should be used instead. Independent writers agree (a well-designed compound index almost always beats intersection; 'if you think you need index intersection, you probably need better compound indexes'). — [source](https://web.archive.org/web/2024/https://www.mongodb.com/docs/v6.0/core/index-intersection/) - The following example shows how MongoDB uses bounds intersection to define a smaller range of values to query, resulting in improved query performance. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-bounds-intersection) - 2. Create a multikey index — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-bounds-intersection) - ```javascript db.students.find( { grades : { $elemMatch: { $gte: 90, $lte: 99 } } } ) ``` — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-bounds-intersection) - The following example shows how MongoDB uses compound bounds to define a more efficient query constraint, resulting in improved query performance. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-compound-bounds-of-non-array-field-and-array-field) - 2. Create a compound multikey index — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-compound-bounds-of-non-array-field-and-array-field) - The preceding query specifies a condition on both keys of the index (`item` and `ratings`). — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-compound-bounds-of-non-array-field-and-array-field) - - The query must specify predicates on the fields using `$elemMatch` on that path. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#compound-bounds-of-multiple-fields-from-the-same-array) - 1. Create a compound multikey index — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example) - ```javascript db.survey2.find( { ratings: { $elemMatch: { score: { $lte: 5 }, by: "anon" } } } ) ``` — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example) - The following example demonstrates `$elemMatch` on diverging field paths. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-elemmatch-on-diverging-field-paths) - The following query uses an `$elemMatch` *not* on the required path: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-elemmatch-on-diverging-field-paths) - ```javascript db.survey3.find( { ratings: { $elemMatch: { 'scores.q1': 2, 'scores.q2': 8 } } } ) ``` — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-elemmatch-on-diverging-field-paths) - ```javascript db.survey3.find( { 'ratings.scores': { $elemMatch: { 'q1': 2, 'q2': 8 } } } ) ``` — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#example-elemmatch-on-diverging-field-paths) - **Tip: Remove Redundant Indexes** — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#index-prefixes) - Cardinality ordering of equality fields is contested. A third-party blog says to place high-cardinality fields first and labels { customerId: 1, status: 1 } good. The MongoDB ESR page says equality fields may appear in any order among themselves and does not mention cardinality. — [source](https://oneuptime.com/blog/post/2025-12-15-mongodb-compound-indexes/view) - Large or unbounded arrays make multikey indexes costly: one entry per element, and index build and read performance degrade as arrays grow. Specific write-amplification claims come from a single third-party blog and are unverified. — [source](https://oneuptime.com/blog/post/2026-03-31-mongodb-how-to-avoid-the-unbounded-array-growth-anti-pattern-in-mong/markdown) - Documented partialFilterExpression operators: equality, $exists:true (not false), $gt/$gte/$lt/$lte, $type, $and, $or, $in, $geoWithin, $geoIntersects. Originally (3.1.1) only $exists:true, comparisons, $eq, $type and top-level $and; richer expressions ($elemMatch, $exists:false, $ne/$not) remain an open backlog ticket. — [source](https://mongodb.com/docs/v7.3/core/index-partial) - If a sparse index would return an incomplete result set for a query or sort, the planner does not select it unless hint() names it. Example: find().sort({plot:-1}) on a collection where some documents lack plot ignores a sparse index on plot. With hint({plot:1}) the query returns only documents that have plot. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md) - Hinting a sparse index on a count of all documents (empty predicate) uses the sparse index even though the count is wrong: countDocuments({}, {hint:{rated:1}}) returns only documents that have rated. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md) - Scans that skip duplicate index keys, counted separately based on whether the scan fetches documents. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats/) - Optional. If specified, the index only references documents that match the filter expression. See Partial Indexes for more information. — [source](https://www.mongodb.com/docs/manual/reference/command/createIndexes/) - Optional. If true, the index only references documents with the specified field. These indexes use less space but behave differently in some situations (particularly sorts). The default value is false. See Sparse Indexes for more information. — [source](https://www.mongodb.com/docs/manual/reference/command/createIndexes/) - MongoDB provides the option to create partial indexes. These offer a superset of the functionality of sparse indexes and are preferred instead. — [source](https://www.mongodb.com/docs/manual/reference/command/createIndexes/) - For example, a partial index can implement the same behavior as a sparse index. This partial index supports the same queries as a sparse index on the `name` field: — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#comparison-with-sparse-indexes) - Partial indexes offer a superset of the functionality of sparse indexes and should be preferred over sparse indexes. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-properties.md#partial-indexes) - [1] For single-field indexes, the order of the index doesn't matter. For compound indexes, the field order impacts what queries the index supports. For details, see Compound Index Sort Order. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - For an example of a covered query with a multikey index, see Covered Queries on the multikey indexes page. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - For single-field indexes, the order of the index doesn't matter. For compound indexes, the field order impacts what queries the index supports. For details, see [Compound Index Sort Order.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/sort-order.md#std-label-index-ascending-and-descending) — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#create-indexes-to-support-queries) - For an example of a covered query with a multikey index, see [Covered Queries](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-multikey-covered-queries) on the multikey indexes page. — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#multikey-covering) - [Partial indexes](https://www.mongodb.com/docs/manual/core/index-partial.md#std-label-partial-sparse-index-comparison) can function as sparse indexes, but also support filter expressions for conditions beyond whether a field exists. Use a partial index for greater control if you need precise filtering. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md) - A query can use a partial index only if its predicate includes the partialFilterExpression or a narrower condition; otherwise the planner skips it to avoid incomplete results. Example: index filter rating>5 serves rating>=8 but not rating<8. — [source](https://mongodb.com/docs/v7.3/core/index-partial) - This example creates multiple indexes with the same key pattern and different `sparse` options: — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#unique-and-sparse-index-creation) - Querying *only* the index can be much faster than querying documents outside of the index. Index keys are typically smaller than the documents they catalog, and indexes are typically available in RAM or located sequentially on disk. — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#performance) - In explain() output, an intersection plan appears as an AND_SORTED stage (merges two RecordId-ordered index scans, like a merge join) or an AND_HASH stage (hashes one scan's results and probes with the other, with memory growing with matches). — [source](https://dev.to/franckpachot/mongodb-index-intersection-and-postgresql-bitmap-scan-40a6) - You enforce a unique constraint on [compound indexes](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound). A unique compound index enforces uniqueness on the *combination* of the index key values. — [source](https://www.mongodb.com/docs/manual/core/index-unique/create-compound.md) - A unique index ensures that the indexed fields do not store duplicate values, and that a value appears at most once for a given field. A unique compound index ensures that any given combination of the index key values appears at most once. By default, MongoDB creates a unique index on the [\_id](https://www.mongodb.com/docs/manual/core/document.md#std-label-document-id-field) field during the creation of a collection. — [source](https://www.mongodb.com/docs/manual/core/index-unique.md) - Write amplification: every insert and delete updates all indexes on the collection, and an update that changes an indexed field removes the old entry and adds a new one in each index containing that field. Unused indexes also take WiredTiger cache space. — [source](https://www.netdata.cloud/guides/mongodb/mongodb-unused-indexes/) - - To learn how MongoDB combines multikey index bounds to improve performance, see [Multikey Index Bounds.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#std-label-indexes-multikey-bounds) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#learn-more) - If avoiding in-memory sorts is critical, place sort fields before range fields (ESR) — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - - To learn about multikey index bounds, see [Multikey Index Bounds.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#std-label-indexes-multikey-bounds) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/create-multikey-index-basic.md#learn-more) - Starting in MongoDB 5.0, [unique sparse](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-sparse-unique-index) and [unique non-sparse](https://www.mongodb.com/docs/manual/core/indexes/index-properties.md#std-label-unique-index) indexes with the same [key pattern](https://www.mongodb.com/docs/manual/reference/method/db.collection.createindexes.md#std-label-key_patterns) can exist on a single collection. — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#sparse-and-non-sparse-unique-indexes) - - Indexes loaded in the WiredTiger internal cache have a different data representation to the on-disk format, but can still take advantage of index prefix compression to reduce RAM usage. Index prefix compression deduplicates common prefixes from indexed fields. — [source](https://www.mongodb.com/docs/manual/administration/production-notes.md#cache-configuration-settings) - Starting in MongoDB 5.0, a unique sparse index and a unique non-sparse index with the same key pattern can coexist on one collection, as can sparse and basic indexes with the same key pattern. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md) - A { $exists: true } query on a sparse-indexed field is answered from the index alone with no FETCH. A { $exists: false } query does not use a sparse index and falls to a collection scan. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/exists/) - For example, to create a unique index on `name`, `email`, and `password` fields of the `users` collection, use the following operation in [`mongosh`:](https://www.mongodb.com/docs/mongodb-shell.md#mongodb-binary-bin.mongosh) — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#unique-compound-index) - The unique index permits the insertion of the following documents into the collection since the index enforces uniqueness for the *combination* of `email` and `name` values: — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#unique-compound-index) - Each stage passes its resulting documents or index keys to the parent node. The leaf nodes access the collection or the indices. The internal nodes use the documents or the index keys that result from the child nodes. The root node indicates the stage that MongoDB ultimately derives the result set from. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#classic-execution-engine) - A document that describes the child stage, which provides the documents or index keys to its parent. The field is present *if* the parent stage has only one child. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#unsharded-collections) - An array of documents describing the child stages. Child stages provide the documents or index keys to the parent stage. The field is present *if* the parent stage has multiple child nodes. For example, stages for [$or expressions](https://www.mongodb.com/docs/manual/reference/explain-results.md#std-label-explain-output-or-expression) might consume input from multiple sources. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#unsharded-collections) - - `metadata`: The estimate comes from database metadata like collection cardinality. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#unsharded-collections) - - A unique index stores a null value for a document missing the indexed field; that is a missing index field is treated as another instance of a `null` index key value. For more information, see [Missing Document Field in a Unique Single-Field Index.](https://www.mongodb.com/docs/manual/core/index-unique.md#std-label-unique-index-and-missing-field) — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#sharded-clusters-and-unique-indexes) - | Metric | Description | | --- | --- | | Execution Count | Number of queries executed per hour which would be improved. | | Average Execution Time | Current average execution time in milliseconds for affected queries. | | Average Query Targeting | Average number of documents read per document returned by affected queries. A higher query targeting score indicates a greater degree of inefficiency. For more information on query targeting, see [Query Targeting.](https://www.mongodb.com/docs/atlas/performance-advisor.md#std-label-query-targeting) | | In Memory Sort | Current number of affected quer — [source](https://www.mongodb.com/docs/atlas/performance-advisor.md#index-metrics) - The following operation on the `users` collection creates an index that specifies a [unique constraint](https://www.mongodb.com/docs/manual/core/index-unique.md#std-label-index-type-unique) on the `email` field and a partial filter expression `password: { $exists: true }`. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#partial-index-with-unique-constraint) - The index prevents the insertion of documents that have both an email address that already exists in the collection and an existing `password` field. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#partial-index-with-unique-constraint) - However, you can still insert documents with duplicate `email` addresses if the documents do not include a `password` field. The unique constraint only applies to documents that the index includes. The following example inserts two documents with the same email address: — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#partial-index-with-unique-constraint) - The following table compares `$exists` query performance using sparse and non-sparse indexes: — [source](https://www.mongodb.com/docs/manual/reference/operator/query/exists.md#use-a-sparse-index-to-improve-exists-performance) - MongoDB can also use the index to support a query on the `item` and `stock` fields, since the `item` field corresponds to a prefix. However, the index is not as efficient as `{ item: 1, stock: 1 }`. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#index-prefixes) - Without the `item` field, none of the preceding field combinations correspond to a prefix index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#index-prefixes) - - All the fields returned in the results are in the same index. — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#run-covered-queries) - A `movies` collection has the following index on the `rated` and `title` fields: — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#example) - The index covers the following operation which queries on the `rated` and `title` fields and returns only the `title` field: — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#example) - For the specified index to cover the query, the projection document must explicitly specify `_id: 0` to exclude the `_id` field from the result since the index doesn't include the `_id` field. — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#example) - An index can cover a query on fields within embedded documents. — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#embedded-documents) - The `{ "location.address.city": 1 }` index covers the following query: — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#embedded-documents) - Not all [index types](https://www.mongodb.com/docs/manual/core/indexes/index-types.md#std-label-index-types) can cover queries. For details on covered index support, refer to the documentation page for the corresponding index type. — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#index-types) - The index does not index documents that do not include the `plot` field. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#create-a-sparse-index) - The following operation creates an index with a [unique constraint](https://www.mongodb.com/docs/manual/core/index-unique.md#std-label-index-type-unique) and sparse filter on the `password` field in the `users`: — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#sparse-index-with-unique-constraint) - This index permits inserting documents that either have unique values for the `password` field, or don't include a `password` field. For the example documents in the `users` collection, the index permits the following [insert operations:](https://www.mongodb.com/docs/manual/tutorial/insert-documents.md) — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#sparse-index-with-unique-constraint) - However, the index doesn't allow inserting documents that have email addresses that already exist in the collection. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#sparse-index-with-unique-constraint) - Why the planner avoids intersection (per Franck Pachot's analysis of server source tag r8.2.2): plans that finish their trial first get an 'EOF bonus', intersection plans take a small score penalty, and the productivity metric counts units of work without weighing their cost. Without the penalty the planner would still not pick AND_SORTED. — [source](https://dev.to/franckpachot/mongodb-index-intersection-and-postgresql-bitmap-scan-40a6) - Forcing intersection is possible only via internal, test-only parameters (e.g. internalQueryForceIntersectionPlans) and is not supported; hint() accepts only a single index. A request (SERVER-86940, created 2024-02-22) to accept that flag in persistent query settings was closed Won't Do on 2024-04-25 with no recorded rationale. — [source](https://jira.mongodb.org/browse/SERVER-86940) - A sparse unique index enforces uniqueness only among documents that have the field and allows multiple documents that omit it. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md) - This example adds a unique compound index on the `groupNumber`, `lastname`, and `firstname` fields of a `members` collection. The index ensures that the combination of these field values is unique for each document in the collection. — [source](https://www.mongodb.com/docs/manual/core/index-unique/create-compound.md#about-this-task) - The order of fields listed in the [`databases.[n].collections.[n].dataSources.[n].path`](https://www.mongodb.com/docs/atlas/data-federation/config/adf-config-file-formats.md#mongodb-datalakeconf-aws-datalakeconf-aws.databases.-n-.collections.-n-.dataSources.-n-.path) is important in the same way as it is in [Compound Indexes](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound). The specified path corresponds to data that is partitioned first by the value of the first field, and then by the value of the next field, and so on. — [source](https://www.mongodb.com/docs/atlas/data-federation/admin/optimize-query-performance.md#data-structure-in-s3-simple-storage-service) - The default compound index for the collection indexes the entire `metadata` subdocument, so the index is only used with [`$eq`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/eq.md#mongodb-expression-exp.-eq) queries. By indexing specific `metadata` fields, you improve query performance for other query types. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#use-secondary-indexes-to-improve-sort-performance) - - A sort on `{ : ±1, timeField: ±1 }` uses the default compound index on `{ : ±1, timeField: ±1 }`. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#use-secondary-indexes-to-improve-sort-performance) - compound index on `{ : ±1, timeField: ±1 }` — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#use-secondary-indexes-to-improve-sort-performance) - 1. Creating a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound) on `meta.project` and `meta.type` and supports the aggregation. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-best-practices.md#use-group-instead-of-distinct) - A compound index references multiple fields and can dramatically improve query response times. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - | Parameter | Type | Description | | --- | --- | --- | | `indexBounds` | document | The inclusive lower bound for the index keys. | — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.min.md#definition) - If the `keys` document specifies more than one field, then [`createIndex()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.createindex.md#mongodb-method-db.collection.createIndex) creates a [compound index.](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-compound-index) — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex.md#create-an-ascending-index-on-a-single-field) - The following example creates a compound index on the `year`, `runtime`, and `title` fields: — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex.md#create-an-index-on-multiple-fields) - The following example creates a compound index on the `title` field (in ascending order) and the `runtime` field (hashed): — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex.md#create-an-index-on-multiple-fields) - For example, you can use the following code to create a compound index on the `movies` collection of the `sample_mflix` database specifying the numeric fields `year` and `metacritic` and the string field `title`. The index also specifies the collation locale `"fr"` for string comparisons: — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex.md#create-indexes-with-collation) - Indexes store entries in either ascending (`1`) or descending (`-1`) order. For [compound indexes](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound), sort order can determine whether the index can support a sort operation. The direction of a field in a compound index is relevant only if there is a sort on multiple fields in opposite directions. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/sort-order.md) - A compound index can improve performance for the leaderboard if the sort order in the index matches the sort order in the query. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/sort-order.md#use-case) - MongoDB can traverse a compound index in either direction. If the application allows users to view the leaderboard in reverse order, the index supports that query as well. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/sort-order.md#reverse-results) - Compound indexes collect and sort data from multiple field values from each document in a collection. You can use the compound index to query the first field or any prefix fields of the index. The order of fields in a compound index is very important. The B-tree created by a compound index stores the sorted data in the order that the index specifies the fields. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types.md#compound-index) - For example, the following image shows a compound index where documents are first sorted by `userid` in ascending order (alphabetically). Then, the `scores` for each `userid` are sorted in descending order: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types.md#compound-index) - Starting in MongoDB 6.3, MongoDB creates a default [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound) on both the metaField and timeField of a time series collection. MongoDB uses this index to improve query performance and speed. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-index.md#compound-indexes) - You can add a [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound) on the `timeField`, `metaField`, or measurement fields. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-index.md#compound-indexes) - For example, add the following two compound indexes. The first index orders by `year` field first, and then the `rated` field. The second index orders by `rated` first, and then the `year` field. — [source](https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan.md#mongodb-shell) - In some cases, the query planner uses a `DISTINCT_SCAN` index plan that returns one document per index key value. `DISTINCT_SCAN` executes faster than `IXSCAN` if there are multiple documents per key value. However, index scan parameters might affect the time comparison of `DISTINCT_SCAN` and `IXSCAN`. — [source](https://www.mongodb.com/docs/manual/core/aggregation-pipeline-optimization.md#indexes) - There can be no more than 32 fields in a compound index. — [source](https://www.mongodb.com/docs/manual/reference/limits.md#indexes) - You can specify up to 32 fields in a single compound index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/create-compound-index.md#restriction) - The following operation creates a compound index containing the `title` and `metacritic` fields: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/create-compound-index.md#procedure) - - To learn how sort order (ascending or descending) impacts performance of compound indexes, see [Use Indexes to Sort Query Results.](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#std-label-sorting-with-indexes) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound/create-compound-index.md#learn-more) - You can [create and manage compound indexes in the UI](https://www.mongodb.com/docs/atlas/atlas-ui/indexes/) for deployments hosted in [MongoDB Atlas.](https://www.mongodb.com/docs/atlas) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md) - This section describes technical details and limitations for compound indexes. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#details) - MongoDB automatically creates a compound index on both the metaField and timeField of a time series collection. — [source](https://www.mongodb.com/docs/manual/core/timeseries-collections/) - - To create a [compound index](https://www.mongodb.com/docs/manual/core/index-compound/), click the plus icon next to the index type dropdown. — [source](https://www.mongodb.com/docs/atlas/atlas-ui/indexes.md#create-an-index) - After you create the compound index, `$text` queries only scan documents that match a specified equality condition on the `department` field. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-text/limit-number-of-items-scanned-for-text-search.md#results) - A [compound index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-index-type-compound) references multiple fields and can dramatically improve query response times. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline.md) - Number of index entries scanned. [`explain.executionStats.totalKeysExamined`](https://www.mongodb.com/docs/manual/reference/explain-results.md#mongodb-data-explain.executionStats.totalKeysExamined) corresponds to the `nscanned` field returned by `cursor.explain()` in earlier versions of MongoDB. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#unsharded-collections) - If the query planner selects an index, the explain result includes a `IXSCAN` stage. The stage includes information such as the index key pattern, direction of traversal, and index bounds. — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#collection-scan) - MongoDB automatically disables slot-based execution engine on collections with an index with a hashed path [prefix](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#std-label-compound-index-prefix) of a non-hashed path, where both paths are in the index. — [source](https://www.mongodb.com/docs/manual/reference/sbe.md#eligible-queries-for-the-slot-based-execution-engine) - Creating a compound index on meta.project and meta.type and supports the aggregation. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-best-practices/) - An index can have multiple equality keys. They can appear in any order relative to each other, but all equality keys must precede any sort or range fields. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - An index is unused if it doesn't support any queries. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/drop-indexes.md#unused-indexes) - The Performance Advisor flags an index as unused if it hasn't supported a query in 7 or more days after it was created or the server was restarted. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/drop-indexes.md#unused-indexes) - An index is redundant if another index supports any query that it could. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/drop-indexes.md#redundant-indexes) - The Performance Advisor flags an index as redundant if it matches the prefix of another index in the same collection. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/drop-indexes.md#redundant-indexes) - Detect unused indexes with the $indexStats aggregation stage: an index with accesses.ops of 0 has not been used since the counter's `since` timestamp. — [source](https://mongodb.com/docs/manual/data-modeling/design-antipatterns/unnecessary-indexes/) - Zero read ops does not prove an index is droppable: a unique index can enforce integrity with no reads, and dropping it permits duplicate keys. Netdata also says to exclude the _id index. — [source](https://www.netdata.cloud/guides/mongodb/mongodb-unused-indexes/) - Hide before dropping: db.collection.hideIndex() makes the planner ignore an index; unhideIndex() restores it instantly, avoiding a rebuild. MongoDB's manual workflow is identify, hide, measure, then dropIndexes() if the impact is negative. — [source](https://www.mongodb.com/docs/upcoming/core/index-hidden/) - Dropping an index takes a lock that can block operations (Pythian: write lock on the database; Netdata: exclusive collection lock), so schedule drops in low-traffic windows and keep a backup or the index definition to rebuild. — [source](https://www.pythian.com/blog/how-to-find-unused-index-and-drop-them-safely-in-mongodb) - Query selectivity is how well a predicate filters out documents. An equality match on unique _id is highly selective. $ne and $nin match much of the index, so with an index they may perform no better than a collection scan. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - You can specify a sort on all the keys of the index or on a subset; however, the sort keys must be listed in the same order as they appear in the index. For example, an index key pattern { a: 1, b: 1 } can support a sort on { a: 1, b: 1 } but not on { b: 1, a: 1 }. — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) - If an ascending or a descending index is on a single field, the sort operation on the field can be in either direction. — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#sort-with-a-single-field-index) - For example, create an ascending index on the field `a` for a collection `records`: — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#sort-with-a-single-field-index) - This index can support an ascending sort on `a`: — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#sort-with-a-single-field-index) - The index can also support the following descending sort on `a` by traversing the index in reverse order: — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#sort-with-a-single-field-index) - This image shows an index on a single field, `score`: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types.md#single-field-index) - Indexes are special data structures that improve query performance. Indexes store a portion of a collection's data in an easy-to-traverse form. The index stores the value of a specific field or set of fields, ordered by the value of the field. — [source](https://www.mongodb.com/docs/atlas/atlas-ui/indexes.md) - You can create an index on a single field to improve performance for queries on that field. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-single/create-single-field-index.md) - The index stores the value of a specific field or set of fields, ordered by the value of the field. The ordering of the index entries supports efficient equality matches and range-based query operations. In addition, MongoDB can return sorted results using the ordering in the index. — [source](https://www.mongodb.com/docs/manual/indexes.md#details) - The difference between the number of matching documents and the number of examined documents may suggest that the query might benefit from the use of an index to improve efficiency. — [source](https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan.md#mongodb-shell) - Multikey indexes collect and sort data from fields containing array values. These indexes improve performance for queries on array fields. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - MongoDB can create multikey indexes over arrays that hold both scalar values (for example, strings and numbers) and embedded documents. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - You can create and manage multikey indexes in the UI for deployments hosted in MongoDB Atlas. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - This section describes technical details and limitations for multikey indexes. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - However, after you create the compound multikey index, if you attempt to insert a document where both genres and year fields are arrays, the insert fails. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - MongoDB can use the multikey index to find documents that have "Drama" at any position in the genres array. Then, MongoDB retrieves these documents and filters for documents whose genres array equals the query array [ "Drama" ]. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - The $expr operator does not support multikey indexes. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - Sort operations that use an index often have better performance than in-memory sorts. For more information on creating indexes to support sort operations, see [Use Indexes to Sort Query Results.](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#std-label-sorting-with-indexes) — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.sort.md#sort-and-index-use) - You can specify a partialFilterExpression option for all MongoDB index types. — [source](https://www.mongodb.com/docs/manual/reference/command/createIndexes/) - - The index [boundaries](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#std-label-multikey-index-bounds-intersecting) for all sort fields are `[MinKey, MaxKey]`. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#sorting) - For example, the following operation creates a multikey index on the `movies` collection on the `genres` field: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#query-on-an-array-field-as-a-whole) - The [`$expr`](https://www.mongodb.com/docs/manual/reference/operator/query/expr.md#mongodb-query-op.-expr) operator does not support multikey indexes. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#expr) - Sort operations that use an index often have better performance than in-memory sorts. — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) - The following query and sort operations use the index prefixes to sort the results. These operations do not need to sort the result set in memory. — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) - In such cases, MongoDB can use the index to retrieve the documents in order specified by the sort. As the example shows, the index prefix in the query predicate can be different from the prefix in the sort. — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) - You can create [partial indexes](https://www.mongodb.com/docs/manual/core/index-partial.md#std-label-index-type-partial) with [`$or`](https://www.mongodb.com/docs/manual/reference/operator/query/or.md#mongodb-query-op.-or). Use the `partialFilterExpression` of the [db.collection.createIndex()](https://www.mongodb.com/docs/manual/reference/method/db.collection.createindex.md#std-label-method-createIndex) method to create a partial index. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/or.md#or-and-partial-indexes) - Partial indexes only index the documents in a collection that meet a specified filter expression. Partial indexes have lower storage requirements and reduced performance costs for index creation and maintenance. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md) - The following example adds a partial index on the `title` and `genres` fields. The operation only indexes documents where the `rating` field is `PG`: — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#create-a-partial-index-on-a-collection) - The following query on the `movies` collection uses the partial index to return the writers of the all movies titled "The Three Musketeers": — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#create-a-partial-index-on-a-collection) - Partial indexes only index the documents in a collection that meet a specified filter expression. If you specify both the `partialFilterExpression` and a [unique constraint](https://www.mongodb.com/docs/manual/core/index-unique.md#std-label-index-type-unique), the unique constraint only applies to the documents that meet the filter expression. A partial index with a unique constraint does not prevent the insertion of documents that do not meet the unique constraint if the documents do not meet the filter criteria. — [source](https://www.mongodb.com/docs/manual/core/index-partial.md#partial-index-with-unique-constraint) - Indexes can also [cover](https://www.mongodb.com/docs/manual/core/query-optimization.md#std-label-covered-queries) [`db.collection.distinct()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#mongodb-method-db.collection.distinct) operations. See [Run Covered Queries](https://www.mongodb.com/docs/manual/core/query-optimization.md#std-label-covered-queries) for more information on queries covered by indexes. — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#index-use) - [Partial indexes](https://www.mongodb.com/docs/manual/core/index-partial.md#std-label-index-type-partial) only index the documents in a collection that meet a specified filter expression. Partial indexes have lower storage requirements and reduced performance costs for index creation and maintenance. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-properties.md#partial-indexes) - MongoDB combines bounds by either [intersecting](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#std-label-multikey-index-bounds-intersecting) or [compounding](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md#std-label-multikey-index-bounds-compound) bounds. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/multikey-index-bounds.md) - "Sort" determines the order for results. To avoid in-memory sorts, put sort fields before range in the index. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - If $in has fewer than 201 array elements, the elements are expanded and then merged in the sort order specified for the index using a SORT_MERGE stage. This improves performance for small arrays. In this case, $in is similar to an equality predicate with ESR. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - If $in has 201 elements or more, the elements are ordered like a range operator. In this case, the performance improvement for small arrays isn't realized. It isn't possible for the subsequent fields in the index to be used for sorting, and $in is similar to a range predicate with ESR. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - Indexes can also cover distinct operations. See Run Covered Queries for more information on queries covered by indexes. — [source](https://www.mongodb.com/docs/manual/reference/command/distinct/) - Also, the Performance Advisor suggests the index only if the difference between scanned documents and returned documents is greater than 500 for impacted queries. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/index-ranking.md#limiting-proposed-indexes) - See Sort and Index Use for more information on in-memory sort operations. — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.allowDiskUse/) - cursor.allowDiskUse() has no effect on sort operations answered using an index or non-indexed ("in-memory") sort operations which require less than 100 megabytes of memory. For more complete documentation on in-memory sorts and sort index use, see Sort and Index Use. — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.allowDiskUse/) - | Example | Index Prefix | | --- | --- | | `db.data.find().sort( { a: 1 } )` | `{ a: 1 }` | | `db.data.find().sort( { a: -1 } )` | `{ a: 1 }` | | `db.data.find().sort( { a: 1, b: 1 } )` | `{ a: 1, b: 1 }` | | `db.data.find().sort( { a: -1, b: -1 } )` | `{ a: 1, b: 1 }` | | `db.data.find().sort( { a: 1, b: 1, c: 1 } )` | `{ a: 1, b: 1, c: 1 }` | | `db.data.find( { a: { $gt: 4 } } ).sort( { a: 1, b: 1 } )` | `{ a: 1, b: 1 }` | — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#sort-and-index-prefix) - | Example | Index Prefix | | --- | --- | | `db.data.find( { a: 5 } ).sort( { b: 1, c: 1 } )` | `{ a: 1 , b: 1, c: 1 }` | | `db.data.find( { b: 3, a: 4 } ).sort( { c: 1 } )` | `{ a: 1, b: 1, c: 1 }` | | `db.data.find( { a: 5, b: { $lt: 3} } ).sort( { b: 1 } )` | `{ a: 1, b: 1 }` | — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#sort-and-non-prefix-subset-of-an-index) - If the options specification had been split into multiple documents like this: `{ unique: true }, { sparse: true, expireAfterSeconds: 3600 }` the index creation operation would have failed. — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndex.md#options) - 2d indexes are always [sparse](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) and ignore the [sparse](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) option. If a document lacks a 2d index field (or the field is `null` or an empty array), MongoDB does not add an entry for the document to the 2d index. For inserts, MongoDB inserts the document but does not add to the 2d index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2d.md#sparse-property) - The index covers a smaller location range and has increased performance than a default 2d index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2d/create/define-location-range.md#results) - Queries that use `{ $exists: true }` on fields that use a non-sparse index or that use `{ $exists: true }` on fields that are not indexed examine all documents in a collection. To improve performance, create a [sparse index](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) on the `field` as shown in the following scenario: — [source](https://www.mongodb.com/docs/manual/reference/operator/query/exists.md#use-a-sparse-index-to-improve-exists-performance) - 2. Create a [sparse index](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) on the `metacritic` field: — [source](https://www.mongodb.com/docs/manual/reference/operator/query/exists.md#use-a-sparse-index-to-improve-exists-performance) - 3. The following example counts the documents where the `metacritic` field has a value (including null) and uses the [sparse index:](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) — [source](https://www.mongodb.com/docs/manual/reference/operator/query/exists.md#use-a-sparse-index-to-improve-exists-performance) - An aggregation pipeline can use [indexes](https://www.mongodb.com/docs/manual/indexes.md#std-label-indexes) from the input collection to improve performance. Using an index limits the amount of documents a stage processes. Ideally, an index can [cover](https://www.mongodb.com/docs/manual/core/query-optimization.md#std-label-read-operations-covered-query) the stage query. A covered query has especially high performance, since the index returns all matching documents. — [source](https://www.mongodb.com/docs/manual/core/aggregation-pipeline-optimization.md#indexes) - - An index on the grouping field that matches the `$sort` order returns all of the field values needed for the `$group` stage, making it a covered query. — [source](https://www.mongodb.com/docs/manual/core/aggregation-pipeline-optimization.md#indexes) - The number of new multikey metadata paths recorded for ordinary indexes while recording the path during a multi-document transaction. — [source](https://www.mongodb.com/docs/manual/reference/command/serverStatus.md#indexstats) - The number of new multikey metadata paths recorded for ordinary indexes while recording the path outside a multi-document transaction. — [source](https://www.mongodb.com/docs/manual/reference/command/serverStatus.md#indexstats) - The number of side transactions used to record multikey path changes for ordinary indexes. — [source](https://www.mongodb.com/docs/manual/reference/command/serverStatus.md#indexstats) - If the value of a field returned from a query that is [covered by an index](https://www.mongodb.com/docs/manual/core/query-optimization.md#std-label-covered-queries) is `NaN`, the type of that `NaN` value is *always* `double`. — [source](https://www.mongodb.com/docs/manual/reference/limits.md#indexes) - - To learn how to create a multikey index on embedded document fields, see [Create an Index on an Embedded Field in an Array.](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/create-multikey-index-embedded.md#std-label-index-create-multikey-embedded) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/create-multikey-index-basic.md#learn-more) - For example, create a unique compound multikey index on `email` and `name`: — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#unique-constraint-across-separate-documents) - If you use both the `partialFilterExpression` and a unique constraint, the unique constraint only applies to documents that meet the filter expression. For an example, see [Partial Index with Unique Constraint.](https://www.mongodb.com/docs/manual/core/index-partial.md#std-label-partial-index-with-unique-constraints) — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#unique-partial-indexes) - - The default `_id` index; however, the `_id` index only enforces the uniqueness constraint **per shard** if the `_id` field is not the shard key. — [source](https://www.mongodb.com/docs/manual/core/index-unique.md#sharded-clusters-and-unique-indexes) - | Name | Type | Required | Description | | --- | --- | --- | --- | | --clusterName | string | true | Name of the cluster. To learn more, see [https://dochub.mongodb.org/core/create-cluster-api.](https://dochub.mongodb.org/core/create-cluster-api) | | --collection | string | false | Name of the collection. Mutually exclusive with --file. | | --db | string | false | Name of the database. Mutually exclusive with --file. | | -f, --file | string | false | Path to an optional JSON configuration file that defines index settings. Note: Unsupported fields in the JSON file are ignored. Mutually exclusiv — [source](https://www.mongodb.com/docs/atlas/cli/current/command/atlas-clusters-indexes-create.md#options) - Hash-based index intersection stages, counted separately based on whether the stage applies a filter. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats/) - Sorted index intersection stages. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats/) - MongoDB's indexing strategy eliminates any need to arrange exact match fields in a particular order. However, if the query does not specify an equality condition on an index prefix that precedes or overlaps with the sort specification, the operation will not efficiently use the index. For more information, see [Sort and Non-prefix Subset of an Index.](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes.md#std-label-sort-index-nonprefix-subset) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-compound.md#index-prefixes) - The hashing function does not support [multikey indexes:](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multikey) — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-hashed.md#array-fields) - You can create partial indexes with $or. Use the partialFilterExpression of the db.collection.createIndex() method to create a partial index. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/or/) - `searchMeta` returns different [types of metadata result](https://www.mongodb.com/docs/search/query/aggregation-stages/searchMeta.md#std-label-fts-metadata-results) documents for MongoDB Search queries on the field or fields in an Atlas collection. The fields must be covered by a [MongoDB Search](https://www.mongodb.com/docs/atlas/reference/atlas-search/index-definitions/) index. See: — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/searchMeta.md) - - You can only create [multikey indexes](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multikey) on the `metaField`. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-limitations.md#time-series-secondary-indexes) - - You can only create [sparse indexes](https://www.mongodb.com/docs/manual/core/index-sparse.md#std-label-index-type-sparse) on the `metaField`. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-limitations.md#time-series-secondary-indexes) - The following operation creates an ascending multikey index on the `stock.quantity` field of the `inventory` collection: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/create-multikey-index-embedded.md#procedure) - Do not need a sparse index on the field. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/exists/) - While 2d indexes do not support more than one location field in a document, you can use a [multi-key index](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#std-label-index-type-multi-key) to index multiple coordinate pairs in a single document. For example, in the following document, the `locs` field holds an array of coordinate pairs: — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2d/internals.md#multi-location-documents-for-2d-indexes) - See [Sort and Index Use](https://www.mongodb.com/docs/manual/reference/method/cursor.sort.md#std-label-sort-index-use) for more information on in-memory sort operations. — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.allowDiskUse.md#definition) - [`cursor.allowDiskUse()`](https://www.mongodb.com/docs/manual/reference/method/cursor.allowdiskuse.md#mongodb-method-cursor.allowDiskUse) has no effect on sort operations answered using an index *or* non-indexed ("in-memory") sort operations which require less than 100 megabytes of memory. For more complete documentation on in-memory sorts and sort index use, see [Sort and Index Use.](https://www.mongodb.com/docs/manual/reference/method/cursor.sort.md#std-label-sort-index-use) — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.allowDiskUse.md#supports-large-non-indexed-sorts-only) - For example, the following operation in [`mongosh`](https://www.mongodb.com/docs/mongodb-shell.md#mongodb-binary-bin.mongosh) creates a sparse index on the `plot` field of the `movies` collection: — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#create-a-sparse-index) - For example, create a sparse index on the `rated` field on the `movies` collection. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#sparse-index-and-incomplete-results) - An index that is both sparse and [unique](https://www.mongodb.com/docs/manual/core/index-unique.md#std-label-index-type-unique) prevents a collection from having documents with duplicate values for a field but allows multiple documents that omit the key. — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#sparse-and-unique-properties) - The following example creates a sparse index on the `password` field: — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#create-a-sparse-index-on-a-collection) - Then, the following query on the `users` collection uses the sparse index to return the documents that have the `password` field: — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#create-a-sparse-index-on-a-collection) - The following example creates a sparse index on the `plot` field: — [source](https://www.mongodb.com/docs/manual/core/index-sparse.md#sparse-index-on-a-collection-cannot-return-complete-results) - The MongoDB 6.0 manual states that the query optimizer rarely selects index intersection plans, that hash-based index intersection is disabled by default, and that sort-based intersection is disfavored in plan selection, to prevent bad plan selection. — [source](https://web.archive.org/web/2024/https://www.mongodb.com/docs/v6.0/core/index-intersection/) - The dedicated Index Intersection page no longer resolves in the live manual: the 4.4 and 6.0 URLs now serve the Indexes overview page. The 6.0 text survives in the Wayback Machine only, so cite versioned or archived copies. — [source](https://web.archive.org/web/2024/https://www.mongodb.com/docs/v6.0/core/index-intersection/) - A unique partial index enforces uniqueness only among documents matching the filter; non-matching documents may share key values (e.g. unique email filtered on password $exists:true, or on status:'active'). — [source](https://mongodb.com/docs/v7.3/core/index-partial) - Index searches make efficient use of exact matches to reduce the number of index keys examined. Equality fields must come first. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - Sort operations on time series collections can use secondary indexes on the `timeField` field. Under certain conditions, sort operations can also use compound secondary indexes on the `metaField` and `timeField` fields. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#use-secondary-indexes-to-improve-sort-performance) - The preceding index, while technically correct, isn't structured to optimize query performance. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - The more selective the equality matches, the more efficient the indexed query. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - The fields correspond to *all* the keys of a particular index *in order*. — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.max.md#parameters) - The following operation specifies multiple index fields to do a compound sort within the `$sortArray` operation: — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sortArray.md#sort-on-multiple-fields) - - Faster queries on clustered collections without needing a secondary index, such as queries with range scans and equality comparisons on the clustered index key. — [source](https://www.mongodb.com/docs/manual/core/clustered-collections.md#benefits) - - A sort on `{ : ±1 }` uses a secondary index on ``. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#use-secondary-indexes-to-improve-sort-performance) - - Sort operations on an indexed field can return documents pre-sorted by the index. — [source](https://www.mongodb.com/docs/atlas/atlas-ui/indexes.md) - The [`indexMaxNumGeneratedKeysPerDocument`](https://www.mongodb.com/docs/manual/reference/parameters.md#mongodb-parameter-param.indexMaxNumGeneratedKeysPerDocument) setting limits the maximum number of keys generated for a single document to prevent out of memory errors. If an operation requires more keys than the `indexMaxNumGeneratedKeysPerDocument` parameter specifies, the operation fails. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/restrictions.md#number-of-index-keys) - - Use the timeField and other indexed fields for range queries. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-best-practices.md#additional-index-best-practices) - If you use [`min()`](https://www.mongodb.com/docs/manual/reference/method/cursor.min.md#mongodb-method-cursor.min) with [`max()`](https://www.mongodb.com/docs/manual/reference/method/cursor.max.md#mongodb-method-cursor.max) to specify a range: — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.min.md#index-bounds) - - the bound specified by [`max()`](https://www.mongodb.com/docs/manual/reference/method/cursor.max.md#mongodb-method-cursor.max) must be greater than the bound specified by [`min()`.](https://www.mongodb.com/docs/manual/reference/method/cursor.min.md#mongodb-method-cursor.min) — [source](https://www.mongodb.com/docs/manual/reference/method/cursor.min.md#index-bounds) - - The value returned depends on how the database decides to represent values in an index and may change across versions. The represented value may not be the actual value for the field. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/meta.md#return-value) - - The value returned depends on the execution plan chosen by the system. For example, if there are two possible indexes which can be used to answer the query, then the value of the "indexKey" metadata depends on which index is selected. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/meta.md#return-value) - By default, the [clustered index](https://www.mongodb.com/docs/manual/reference/method/db.createcollection.md#std-label-db.createCollection.clusteredIndex) key values are the unique document [object identifiers.](https://www.mongodb.com/docs/manual/reference/bson-types.md#std-label-objectid) — [source](https://www.mongodb.com/docs/manual/core/clustered-collections.md#set-your-own-clustered-index-key-values) - - Large keys increase the storage size of the clustered collection and its secondary indexes which decreases clustered collection performance. — [source](https://www.mongodb.com/docs/manual/core/clustered-collections.md#set-your-own-clustered-index-key-values) - - `"name": "orders clustered key"`, which sets the clustered index name. — [source](https://www.mongodb.com/docs/manual/core/clustered-collections.md#date-clustered-index-key-example) - If you use the `_id` field in a range query, performance is improved. For example, the following query uses `_id` and [`$gt`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/gt.md#mongodb-expression-exp.-gt) to return the orders where the order date is greater than the supplied date: — [source](https://www.mongodb.com/docs/manual/core/clustered-collections.md#date-clustered-index-key-example) - If the operation is successful but the `hidden` value has not changed (specifically, hiding an already hidden index or unhiding an already unhidden index), the command omits the `hidden_old` and `hidden_new` fields from the output. — [source](https://www.mongodb.com/docs/manual/reference/command/collMod.md#hide-an-index-from-the-query-planner) - - `EXPRESS` stages for a limited set of queries that can bypass regular query planning to use optimized index scan plans *(New in version 8.0.)* — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#classic-execution-engine) - A boolean that specifies whether MongoDB applied an [index filter](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-index-filters) for the [plan cache query shape.](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-plan-cache-query-shape) — [source](https://www.mongodb.com/docs/manual/reference/explain-results.md#unsharded-collections) - As the last operation shows, only the index fields preceding the sort subset must have the equality conditions in the query document; the other index fields may specify other conditions. — [source](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) - The index boundaries for all sort fields are [MinKey, MaxKey]. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - You can create indexes on embedded documents as a whole. However, only queries that specify the **entire** embedded document use the index. Queries on a specific field within the document do not use the index. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-single/create-embedded-object-index.md) - Specifies the index's fields. For each field, specify a key-value pair in which the key is the name of the field to index and the value is either the index direction or index type. If specifying direction, specify 1 for ascending or -1 for descending. — [source](https://www.mongodb.com/docs/manual/reference/command/createIndexes/) - Ratio of in-bounds and out-of-bounds index keys examined to indexes containing documents returned by queries with the given query shape. — [source](https://www.mongodb.com/docs/atlas/query-shape-insights/) - The following sort operation on the `timestamp` field uses the Secondary Index to improve performance: — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-secondary-index.md#use-secondary-indexes-to-improve-sort-performance) - - No boundaries for any multikey-indexed field have the same path prefix as the sort pattern. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey.md#sorting) - The first three indexes are on single fields and in ascending order (`1`). — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.createIndexes.md#create-multiple-indexes) - If your application repeatedly runs queries on the same field, you can create an index on that field to improve performance. For example, your human resources department often needs to look up employees by employee ID. You can create an index on the employee ID field to improve the performance of that query. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-single.md#use-cases) - Defining a smaller location range for a 2d index reduces the amount of data stored in the index, and can improve query performance. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/geospatial/2d/create/define-location-range.md#about-this-task) - The type of query operation in the query shape affects the order of the fields used to construct the index. In general, the Performance Advisor ranks fields by their [cardinality.](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-cardinality) — [source](https://www.mongodb.com/docs/atlas/performance-advisor/index-ranking.md#index-field-order) - - Optimize your indexes and remove unused or inefficient indexes. Too many indexes can negatively impact write performance. — [source](https://www.mongodb.com/docs/atlas/analyze-slow-queries.md#best-practices-for-query-performance) - The type of query operation in the query shape affects the order of the fields used to construct the index. In general, the Performance Advisor ranks fields by their cardinality. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/index-ranking/) - The Performance Advisor inspects only the 20 most active collections for unused indexes. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/drop-indexes.md#unused-indexes) - In the Performance Advisor, redundant indexes are marked with a red `Redundant` badge. Below each redundant index, the Performance Advisor displays the related indexes that cover it. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/drop-indexes.md#redundant-indexes) - Related indexes are displayed for you to verify that the redundant index can be dropped safely. Related indexes are not recommended for removal. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/drop-indexes.md#redundant-indexes) - The selectivity of regular expressions depends on the expressions themselves. For details, see regular expression and index use. — [source](https://www.mongodb.com/docs/manual/core/query-optimization/) - The selectivity of [`regular expressions`](https://www.mongodb.com/docs/manual/reference/operator/query/regex.md#mongodb-query-op.-regex) depends on the expressions themselves. For details, see [regular expression and index use.](https://www.mongodb.com/docs/manual/reference/operator/query/regex.md#std-label-regex-index-use) — [source](https://www.mongodb.com/docs/manual/core/query-optimization.md#create-selective-queries) - Not all index types are compatible with all index properties. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-properties.md) - Existing indexes are inefficient or not optimal for this specific query. To learn more, see Indexes. — [source](https://www.mongodb.com/docs/atlas/tutorial/query-profiler/) - How Performance Advisor Suggests and Ranks Indexes — [source](https://www.mongodb.com/docs/atlas/performance-advisor/index-ranking/) - The following aggregation operation uses the [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) stage to retrieve the distinct `rated` values from the `movies` collection: — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#retrieve-distinct-values) - The following [`find()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.find.md#mongodb-method-db.collection.find) operation queries for all documents where the value of the `zipcode` field is `"63109"`. The projection includes the **first** matching element of the `students` array where the `school` field has a value of `102` **and** the `age` field is greater than `10`: — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#elemmatch-https-www-mongodb-com-docs-manual-reference-operator-projection-elemmatch-md-mongodb-projection-proj-elemmatch-with-multiple-fields) - The operation returns the three documents that have `zipcode` value of `"63109"`. The returned documents include the `_id` field and matching elements of the `athletics` array, if any. The operation returns the three documents that have `zipcode` value of `"63109"`. The returned documents include the `_id` field and matching elements of the `athletics` array, if any. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#elemmatch-https-www-mongodb-com-docs-manual-reference-operator-projection-elemmatch-md-mongodb-projection-proj-elemmatch-with-multiple-fields) - For information on [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) and array fields, see the [Behavior](https://www.mongodb.com/docs/manual/reference/command/distinct.md#std-label-distinct-command-array-behavior) section. — [source](https://www.mongodb.com/docs/manual/reference/command/distinct.md#return-distinct-values-for-an-array-field) - The [`$all`](https://www.mongodb.com/docs/manual/reference/operator/query/all.md#mongodb-query-op.-all) operator exists to support queries on arrays. You can also use `$all` to select against a non-array `field`, as in the following example: — [source](https://www.mongodb.com/docs/manual/reference/operator/query/all.md#use-all-with-elemmatch) - The Performance Advisor doesn't suggest indexes that have more than 16 fields. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/index-ranking.md#limiting-proposed-indexes) - The Performance Advisor lists recommended indexes in order of performance impact, from greatest to least. — [source](https://www.mongodb.com/docs/atlas/performance-advisor/index-ranking.md#example-new-york-city-taxi-data) - The query meets all other covered query requirements. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - - `timeField` values that are close together. The time series collection's `granularity`, `bucketMaxSpanSeconds`, and `bucketRoundingSeconds` parameters control the time span covered by each bucket. For more information, see [Set Granularity for Time Series Data.](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-granularity.md#std-label-timeseries-granularity) — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-bucketing.md#how-bucketing-works) - - Time has moved forward or backward past the covered time span, as indicated by an incoming document timestamp that falls outside of the bucket's bounds. These bounds are determined by the collection's [granularity](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-granularity.md#std-label-timeseries-granularity) setting. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-bucketing.md#closure) - partialFilterExpression: { — [source](https://www.mongodb.com/docs/manual/core/index-ttl/) - If the array elements are documents, you can sort by a document field. Specify the field name and a sort direction, ascending (`1`), or descending (`-1` ). — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sortArray.md#sort-by-document-fields) - The ESR (Equality, Sort, Range) Guideline — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - If the range predicate in your query is very selective, place it before the sort fields to reduce the number of sorted documents and allow an in-memory sort. — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/) - | `granularity` | Covered Time Span | | --- | --- | | `"seconds"` (default) | one hour | | `"minutes"` | 24 hours | | `"hours"` | 30 days | — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-automatic-removal.md#timing-of-delete-operations) - After creation, you can modify granularity or bucket definitions by using the .NET/C# driver `RunCommand()` method to run the [`collMod`](https://www.mongodb.com/docs/manual/reference/command/collmod.md#mongodb-dbcommand-dbcmd.collMod) database command. However, you can only increase the time span covered by each bucket. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-procedures.md#configure-a-time-series-collection) - After creation, you can modify granularity or bucket definitions by using the Java Driver `runCommand()` method to run the [`collMod`](https://www.mongodb.com/docs/manual/reference/command/collmod.md#mongodb-dbcommand-dbcmd.collMod) database command. However, you can only increase the time span covered by each bucket. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-procedures.md#configure-a-time-series-collection) - After creation, you can modify granularity or bucket definitions by using the Node.js Driver `command()` method to run the [`collMod`](https://www.mongodb.com/docs/manual/reference/command/collmod.md#mongodb-dbcommand-dbcmd.collMod) database command. However, you can only increase the time span covered by each bucket. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-procedures.md#configure-a-time-series-collection) - After creation, you can modify granularity or bucket definitions by using the PyMongo Driver `command()` method to run the [`collMod`](https://www.mongodb.com/docs/manual/reference/command/collmod.md#mongodb-dbcommand-dbcmd.collMod) database command. However, you can only increase the time span covered by each bucket. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-procedures.md#configure-a-time-series-collection) - After creation, you can modify granularity or bucket definitions by using the [`collMod`](https://www.mongodb.com/docs/manual/reference/command/collmod.md#mongodb-dbcommand-dbcmd.collMod) database command. However, you can only increase the time span covered by each bucket. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-procedures.md#configure-a-time-series-collection) - ```javascript const uri = ""; const keyVaultClient = new MongoClient(uri); await keyVaultClient.connect(); const keyVaultDB = keyVaultClient.db(keyVaultDatabase); // Drop the Key Vault Collection in case you created this collection // in a previous run of this application. await keyVaultDB.dropDatabase(); const keyVaultColl = keyVaultDB.collection(keyVaultCollection); await keyVaultColl.createIndex( { keyAltNames: 1 }, { unique: true, partialFilterExpression: { keyAltNames: { $exists: true } }, } ); ``` — [source](https://www.mongodb.com/docs/manual/core/queryable-encryption/tutorials/explicit-encryption.md#procedure) - key_vault_client[key_vault_db][key_vault_coll].create_index( [("keyAltNames", ASCENDING)], unique=True, partialFilterExpression={"keyAltNames": {"$exists": True}}, ) ``` — [source](https://www.mongodb.com/docs/manual/core/queryable-encryption/tutorials/explicit-encryption.md#procedure) - MongoCollection keyVaultCollection = keyVaultClient.getDatabase(keyVaultDb).getCollection(keyVaultColl); IndexOptions indexOpts = new IndexOptions().partialFilterExpression(new BsonDocument("keyAltNames", new BsonDocument("$exists", new BsonBoolean(true) ))).unique(true); keyVaultCollection.createIndex(new BsonDocument("keyAltNames", new BsonInt32(1)), indexOpts); keyVaultClient.close(); ``` — [source](https://www.mongodb.com/docs/manual/core/queryable-encryption/tutorials/explicit-encryption.md#procedure) - Time has moved forward or backward past the covered time span, as indicated by an incoming document timestamp that falls outside of the bucket's bounds. These bounds are determined by the collection's granularity setting. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-bucketing/) - Once you set a collection's `granularity` or the custom bucketing parameters `bucketMaxSpanSeconds` and `bucketRoundingSeconds`, you can increase the time span covered by a bucket, but not decrease it. Use the [`collMod`](https://www.mongodb.com/docs/manual/reference/command/collmod.md#mongodb-dbcommand-dbcmd.collMod) command to modify the parameters. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-limitations.md#modifying-bucket-parameters) - { partialFilterExpression: { type: "apparel" } } — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats/) - partialFilterExpression: { type: 'apparel' } — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/indexStats/) - In sharded clusters, you can create zones of sharded data based on the shard key. You can associate each zone with one or more shards in the cluster. A shard can associate with any number of zones. In a balanced cluster, MongoDB migrates chunks covered by a zone only to those shards associated with the zone. — [source](https://www.mongodb.com/docs/manual/sharding/) - { name: "metacriticSparseIndex", sparse: true } — [source](https://www.mongodb.com/docs/manual/reference/operator/query/exists/) - Insert two documents, one of which matches the filter expression `{ type: "click" }` of the `partialFilterExpression`: — [source](https://www.mongodb.com/docs/manual/tutorial/expire-data.md#expire-documents-with-filter-conditions) - MongoDB deletes (expires) the document that matches the `partialFilterExpression` of `{ type: "click" }`. As a result, only the document that does not match the filter remains: — [source](https://www.mongodb.com/docs/manual/tutorial/expire-data.md#expire-documents-with-filter-conditions) - For more information on behavior for retrieving distinct values, see the [distinct command behavior.](https://www.mongodb.com/docs/manual/reference/command/distinct.md#std-label-distinct-command-behavior) — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#retrieve-distinct-values) - The query does not include $elemMatch. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-multikey/) - You must use the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) operator if you need separate conditions for selecting documents and for choosing fields within those documents. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/positional.md#array-field-limitations) - You can use `$elemMatch` for deployments hosted in the following environments: — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#compatibility) - Query without `$elemMatch`: — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#example-1) - - First query has a single `` condition in `$elemMatch`. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#example-2) - - Second query omits `$elemMatch`. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#example-2) - First query with `$elemMatch`: — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#example-2) - Second query without `$elemMatch`: — [source](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#example-2) - If the attempt is unsuccessful, MongoDB applies a set of rules to decide whether the multi-planner should continue or if CBR should evaluate the plans to determine the optimal solution. CBR evaluates each node in a plan based on a cost function and its cardinality estimations. MongoDB selects the plan with the lowest overall cost as the winning plan. Currently, MongoDB only invokes the CBR for a small amount of queries. — [source](https://www.mongodb.com/docs/manual/core/query-plans.md#query-plan-options) - The overview is based on [sampling](https://www.mongodb.com/docs/compass/sampling.md#std-label-compass-sampling) the documents in the collection. The schema overview may include additional data about the contents of the fields, such as the minimum and maximum values of dates and integers, the frequency of occurrence of particular values, and the cardinality of the data. — [source](https://www.mongodb.com/docs/compass/schema.md) - Regardless of the ordering of the fields in the document, the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) projection of an existing field returns the field after the other existing field inclusions. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#field-order) - - [`db.collection.find()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.find.md#mongodb-method-db.collection.find) operations on [views](https://www.mongodb.com/docs/manual/core/views.md#std-label-views-landing-page) do not support [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) projection operator. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#restrictions) - The examples on the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) projection operator assumes a collection `schools` with the following documents: — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#examples) - The following [`find()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.find.md#mongodb-method-db.collection.find) operation queries for all documents where the value of the `zipcode` field is `"63109"`. The [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) projection returns only the **first** matching element of the `students` array where the `school` field has a value of `102`: — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#zipcode-search) - The operation returns the following documents that have a `zipcode` value of `"63109"` and projects the `students` array using [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch): The operation returns the following documents that have a `zipcode` value of `"63109"` and projects the `students` array using [`$elemMatch`:](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#zipcode-search) - The [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch) projection can specify criteria on multiple fields. — [source](https://www.mongodb.com/docs/manual/reference/operator/projection/elemMatch.md#elemmatch-https-www-mongodb-com-docs-manual-reference-operator-projection-elemmatch-md-mongodb-projection-proj-elemmatch-with-multiple-fields) - | Parameter | Type | Description | | --- | --- | --- | | `field` | string | The field for which to return distinct values. | | `query` | document | A query that specifies the documents from which to retrieve the distinct values. | | `options` | document | Optional. A document that specifies the options. See [Options.](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#std-label-distinct-method-options) | — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#syntax) - The following example returns distinct values for the `type` field: — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#return-distinct-values-for-a-field) - The following example returns distinct values for the embedded `imdb.rating` field: — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#return-distinct-values-for-an-embedded-field) - The following example returns distinct values for the `genres` array field: — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#return-distinct-values-for-an-array-field) - The following example returns distinct values for the `rated` field where `year` equals `2000`: — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#specify-query-with-distinct) - Using the `metaField` to shard your collection provides sufficienct cardinality as a shard key for time series collections. — [source](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-best-practices.md#use-the-metafield-as-your-shard-key) - Finds the distinct values for a specified field across a single collection. Returns a document containing an array of distinct values and an embedded document containing query statistics and the query plan. — [source](https://www.mongodb.com/docs/manual/reference/command/distinct/) - The name of the collection to query for distinct values. — [source](https://www.mongodb.com/docs/manual/reference/command/distinct/) - The field for which to return distinct values. — [source](https://www.mongodb.com/docs/manual/reference/command/distinct/) - Optional. A query that specifies the documents from which to retrieve the distinct values. — [source](https://www.mongodb.com/docs/manual/reference/command/distinct/) - Results must not be larger than the maximum BSON size. If your results exceed the maximum BSON size, use the aggregation pipeline to retrieve distinct values using the $group operator, as described in Retrieve Distinct Values with the Aggregation Pipeline. — [source](https://www.mongodb.com/docs/manual/reference/command/distinct/) - Return Distinct Values for an Array Field — [source](https://www.mongodb.com/docs/manual/reference/command/distinct/) - Return Distinct Values for an Embedded Field — [source](https://www.mongodb.com/docs/manual/reference/command/distinct/) - | Projection | Description | | --- | --- | | `: <1 or true>` | Specifies the inclusion of a field. If you specify a non-zero integer for the projection value, the operation treats the value as `true`. | | `: <0 or false>` | Specifies the exclusion of a field. | | `".$": <1 or true>` | Uses the [`$`](https://www.mongodb.com/docs/manual/reference/operator/projection/positional.md#mongodb-projection-proj.-) array projection operator to return the first element that matches the query condition on the array field. If you specify a non-zero integer for the projection value, the — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.findOneAndUpdate.md#projection) - Only apply the [`$where`](https://www.mongodb.com/docs/manual/reference/operator/query/where.md#mongodb-query-op.-where) query operator to top-level documents. The [`$where`](https://www.mongodb.com/docs/manual/reference/operator/query/where.md#mongodb-query-op.-where) query operator will not work inside a nested document, for instance, in an [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) query. — [source](https://www.mongodb.com/docs/manual/reference/operator/query/where.md#elemmatch) - For more examples, see [`$all`](https://www.mongodb.com/docs/manual/reference/operator/query/all.md#mongodb-query-op.-all). See also [`$elemMatch`.](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.find.md#query-for-an-array-element) - ```javascript db.bios.find( { awards: { $elemMatch: { award: "Turing Award", year: { $gt: 1980 } } } } ) ``` — [source](https://www.mongodb.com/docs/manual/reference/method/db.collection.find.md#query-an-array-of-documents) - Number of times histogram-based cardinality estimation was used. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats/) - Number of times sampling-based cardinality estimation was used. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats/) - Number of times heuristic-based cardinality estimation was used. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats/) - Number of times a mixed approach to cardinality estimation was used. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats/) - Number of times metadata-based cardinality estimation was used. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats/) - Number of times code-based cardinality estimation was used. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats/) - [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/projection/elemmatch.md#mongodb-projection-proj.-elemMatch), [`$slice`](https://www.mongodb.com/docs/manual/reference/operator/projection/slice.md#mongodb-projection-proj.-slice), and [`$`](https://www.mongodb.com/docs/manual/reference/operator/projection/positional.md#mongodb-projection-proj.-) are the *only* operators that you can use to project specific elements to include in the returned array. — [source](https://www.mongodb.com/docs/manual/tutorial/project-fields-from-query-results.md#project-specific-array-elements-in-the-returned-array) - The even distribution of documents across buckets depends on the cardinality, or the number of unique values, of the `groupBy` field. If the cardinality is not high enough, the $bucketAuto stage may not evenly distribute the results across buckets. — [source](https://www.mongodb.com/docs/manual/reference/operator/aggregation/bucketAuto.md#behavior) - Depending on the distribution of data in the cluster and the selectivity of the query, mongos may still perform a broadcast operation to fulfill these queries. — [source](https://www.mongodb.com/docs/manual/core/sharded-cluster-query-router/) - The following operation queries the `inventory` collection for documents where the value of the `qty` field is an array whose elements match the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemmatch.md#mongodb-query-op.-elemMatch) criteria: — [source](https://www.mongodb.com/docs/manual/reference/operator/query/all.md#use-all-with-elemmatch) - - The field you choose for your hashed shard key should have a high [cardinality](https://www.mongodb.com/docs/manual/core/sharding-choose-a-shard-key.md#std-label-shard-key-range), meaning a large number of different values. — [source](https://www.mongodb.com/docs/manual/core/indexes/index-types/index-hashed/create.md#choose-a-hashed-shard-key) - Depending on the distribution of data in the cluster and the selectivity of the query, [`mongos`](https://www.mongodb.com/docs/manual/reference/program/mongos.md#mongodb-binary-bin.mongos) may still perform a [broadcast operation](https://www.mongodb.com/docs/manual/core/sharded-cluster-query-router.md#std-label-sharding-mongos-broadcast) to fulfill these queries. — [source](https://www.mongodb.com/docs/manual/core/sharded-cluster-query-router.md#targeted-operations) - The following query filter uses the [`$elemMatch`](https://www.mongodb.com/docs/manual/reference/operator/query/elemMatch.md#mongodb-query-op.-elemMatch) operator to find all documents where at least one value in the `scores` array is greater than `80` and less than `90`: — [source](https://www.mongodb.com/docs/atlas/atlas-ui/query/filter.md#match-by-array-conditions) ## Related concepts - index — is a hypernym of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - $elemMatch — is a related of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - multikey index — is a hyponym of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - index key — is a part of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - query planner — is a related of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - covered query — is a part of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - sparse index — is a hyponym of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - index selectivity — is a measure of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - partial index — is a hyponym of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - index anti-pattern — is a problem of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - sort direction — is a part of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - ESR rule — is a part of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - index prefix — is a part of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - single-field index — is a contrast of MongoDB compound index design: ESR, selectivity, covering and multikey bounds - index intersection — is a part of MongoDB compound index design: ESR, selectivity, covering and multikey bounds