- MongoDB CRUD Operations >
- MongoDB CRUD Concepts >
- Read Operations >
- Query Optimization
Query Optimization¶
Indexes improve the efficiency of read operations by reducing the amount of data that query operations need to process. This simplifies the 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 set of fields, then an index on the queried field or fields can prevent the query from scanning the whole collection to find and return the query results. For more information about indexes, see the complete documentation of indexes in MongoDB.
Example
An application queries the inventory
collection on the
type
field. The value of the type
field is user-driven.
To improve the performance of this query, add an ascending, or a
descending, index to the inventory
collection on the type
field. [1] In the mongo
shell, you can
create indexes using the db.collection.ensureIndex()
method:
This index can prevent the above query on type
from scanning the
whole collection to return the results.
To analyze the performance of the query with an index, see Analyze Query Performance.
In addition to optimizing read operations, indexes can support sort
operations and allow for a more efficient storage utilization. See
db.collection.ensureIndex()
and
Indexing Tutorials for more information about index
creation.
[1] | For single-field indexes, the selection between ascending and descending order is immaterial. For compound indexes, the selection is important. See indexing order for more details. |
Query Selectivity¶
Some query operations are not selective. These operations cannot use indexes effectively or cannot use indexes at all.
The inequality operators $nin
and $ne
are not
very selective, as they often match a large portion of the index. As a
result, in most cases, a $nin
or $ne
query with
an index may perform no better than a $nin
or
$ne
query that must scan all documents in a collection.
Queries that specify regular expressions, with inline JavaScript
regular expressions or $regex
operator expressions, cannot
use an index with one exception. Queries that specify regular
expression with anchors at the beginning of a string can use an
index.
Covering a Query¶
An index covers a query when both of the following apply:
- all the fields in the query are part of an index, and
- all the fields returned in the results are in the same index.
For example, a collection inventory
has the following index on the
type
and item
fields:
This index will cover the following operation which queries on the
type
and item
fields and returns only the item
field:
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 does not include the _id
field.
Performance¶
Because the index contains all fields required by the query, MongoDB can both match the query conditions and return the results using only the index.
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.
Limitations¶
An index cannot cover a query if:
the query is on a sharded collection and run against a primary.
any of the indexed fields in any of the documents in the collection includes an array. If an indexed field is an array, the index becomes a multi-key index index and cannot support a covered query.
any of the indexed field in the query predicate or returned in the projection are fields in embedded documents. [2] For example, consider a collection
users
with documents of the following form:The collection has the following index:
The
{ "user.login": 1 }
index does not cover the following query:However, the query can use the
{ "user.login": 1 }
index to find matching documents.
indexOnly
¶
To determine whether a query is a covered query, use the
explain()
method. If the explain()
output displays true
for the indexOnly
field, an
index covers the query, and MongoDB queries only that index to
match the query and return the results.
For more information see Measure Index Use.
[2] | To index fields in subdocuments, use dot notation. |