Query Optimization

Indexes improve the efficiency of read operations by reducing theamount of data that query operations need to process. This simplifiesthe work associated with fulfilling queries within MongoDB.

Create an Index to Support Read Operations

If your application queries a collection on a particular field or setof fields, then an index on the queried field or a compound index on the set of fields can prevent the query fromscanning the whole collection to find and return the query results. Formore information about indexes, see the complete documentation ofindexes in MongoDB.

Example

An application queries the inventory collection on thetype field. The value of the type field is user-driven.

var typeValue = <someUserInput>;
db.inventory.find( { type: typeValue } );

To improve the performance of this query, add an ascending or adescending index to the inventory collection on the typefield. [1] In the mongo shell, you cancreate indexes using the db.collection.createIndex()method:

db.inventory.createIndex( { type: 1 } )

This index can prevent the above query on type from scanning thewhole collection to return the results.

To analyze the performance of the query with an index, seeAnalyze Query Performance.

In addition to optimizing read operations, indexes can support sortoperations and allow for a more efficient storage utilization. Seedb.collection.createIndex() andIndexes for more information about indexcreation.

Query Selectivity

Query selectivity refers to how well the query predicate excludes orfilters out documents in a collection. Query selectivity can determinewhether or not queries can use indexes effectively or even use indexesat all.

More selective queries match a smaller percentage of documents. Forinstance, an equality match on the unique _id field is highlyselective as it can match at most one document.

Less selective queries match a larger percentage of documents. Lessselective queries cannot use indexes effectively or even at all.

For instance, the inequality operators $nin and$ne are not very selective since they often match a largeportion of the index. As a result, in many cases, a $nin or$ne query with an index may perform no better than a$nin or $ne query that must scan all documents ina collection.

The selectivity of regular expressions depends on theexpressions themselves. For details, see regular expression andindex use.

Covered Query

A covered query is a query that can be satisfied entirely using an indexand does not have to examine any documents. An indexcovers a query when all of thefollowing apply:

• all the fields in the queryare part of an index, and
• all the fields returned in the results are in the same index.
• no fields in the query are equal to null (i.e. {"field" : null} or{"field" : {$eq : null}} ).

For example, a collection inventory has the following index on thetype and item fields:

db.inventory.createIndex( { type: 1, item: 1 } )

This index will cover the following operation which queries on thetype and item fields and returns only the item field:

db.inventory.find(
   { type: "food", item:/^c/ },
   { item: 1, _id: 0 }
)

For the specified index to cover the query, the projection documentmust explicitly specify _id: 0 to exclude the _id field fromthe result since the index does not include the _id field.

Changed in version 3.6: An index can cover a query on fields within embedded documents.[2]

For example, consider a collection userdata with documents of thefollowing form:

{ _id: 1, user: { login: "tester" } }

The collection has the following index:

{ "user.login": 1 }

The { "user.login": 1 } index will cover the query below:

db.userdata.find( { "user.login": "tester" }, { "user.login": 1, _id: 0 } )

Multikey Covering

Starting in 3.6, multikey indexes can cover queries over the non-array fieldsif the index tracks which field or fields cause the index to be multikey.Multikey indexes created in MongoDB 3.4 or later on storage engines otherthan MMAPv1 track this data.

Multikey indexes cannot cover queriesover array field(s).

Performance

Because the index contains all fields required by the query, MongoDB can bothmatch the query conditionsand return the results using only the index.

Querying only the index can be much faster than querying documentsoutside of the index. Index keys are typically smaller than thedocuments they catalog, and indexes are typically available in RAM orlocated sequentially on disk.

Limitations

Restrictions on Indexed Fields

• Geospatial indexes cannotcover a query.

• Multikey indexes cannot cover queriesover array field(s).

See also

Multikey Covering

Restrictions on Sharded Collection

Starting in MongoDB 3.0, an index cannot cover a query on asharded collection when run against amongos if the index does not contain the shard key,with the following exception for the _id index: If a query on asharded collection only specifies a condition on the _id fieldand returns only the _id field, the _id index can cover thequery when run against a mongos even if the _idfield is not the shard key.

In previous versions, an index cannot covera query on a sharded collection when run against amongos.

explain

To determine whether a query is a covered query, use thedb.collection.explain() or the explain()method and review the results.

db.collection.explain() provides information on the executionof other operations, such as db.collection.update(). Seedb.collection.explain() for details.

For more information see Measure Index Use.