OPTIONS

Create Indexes to Support Your Queries

An index supports a query when the index contains all the fields scanned by the query. The query scans the index and not the collection. Creating indexes that support queries results in greatly increased query performance.

This document describes strategies for creating indexes that support queries.

Create a Single-Key Index if All Queries Use the Same, Single Key

If you only ever query on a single key in a given collection, then you need to create just one single-key index for that collection. For example, you might create an index on category in the product collection:

db.products.ensureIndex( { "category": 1 } )

Create Compound Indexes to Support Several Different Queries

If you sometimes query on only one key and at other times query on that key combined with a second key, then creating a compound index is more efficient than creating a single-key index. MongoDB will use the compound index for both queries. For example, you might create an index on both category and item.

db.products.ensureIndex( { "category": 1, "item": 1 } )

This allows you both options. You can query on just category, and you also can query on category combined with item. A single compound index on multiple fields can support all the queries that search a “prefix” subset of those fields.

Example

The following index on a collection:

{ x: 1, y: 1, z: 1 }

Can support queries that the following indexes support:

{ x: 1 }
{ x: 1, y: 1 }

There are some situations where the prefix indexes may offer better query performance: for example if z is a large array.

The { x: 1, y: 1, z: 1 } index can also support many of the same queries as the following index:

{ x: 1, z: 1 }

Also, { x: 1, z: 1 } has an additional use. Given the following query:

db.collection.find( { x: 5 } ).sort( { z: 1} )

The { x: 1, z: 1 } index supports both the query and the sort operation, while the { x: 1, y: 1, z: 1 } index only supports the query. For more information on sorting, see Use Indexes to Sort Query Results.

Starting in version 2.6, MongoDB can use index intersection to fulfill queries. The choice between creating compound indexes that support your queries or relying on index intersection depends on the specifics of your system. See Index Intersection and Compound Indexes for more details.

Create Indexes that Support Covered Queries

A covered query is a query in which:

  • all the fields in the query are part of an index, and
  • all the fields returned in the results are in the same index.

Because the index “covers” the query, MongoDB can both match the query conditions and return the results using only the index; MongoDB does not need to look at the documents, only the index, to fulfill the query. An index can also cover an aggregation pipeline operation on unsharded collections.

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.

MongoDB automatically uses an index that covers a query when possible. To ensure that an index can cover a query, create an index that includes all the fields listed in the query document and in the query result. You can specify the fields to return in the query results with a projection document. By default, MongoDB includes the _id field in the query result. So, if the index does not include the _id field, then you must exclude the _id field (i.e. _id: 0) from the query results.

Example

Given collection users with an index on the fields user and status, as created by the following option:

db.users.ensureIndex( { status: 1, user: 1 } )

Then, this index will cover the following query which selects on the status field and returns only the user field:

db.users.find( { status: "A" }, { user: 1, _id: 0 } )

In the operation, the projection document explicitly specifies _id: 0 to exclude the _id field from the result since the index is only on the status and the user fields.

If the projection document does not specify the exclusion of the _id field, the query returns the _id field. The following query is not covered by the index on the status and the user fields because with the projection document { user: 1 }, the query returns both the user field and the _id field:

db.users.find( { status: "A" }, { user: 1 } )

An index cannot cover a query if:

  • 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 fields are fields in subdocuments. To index fields in subdocuments, use dot notation. For example, consider a collection users with documents of the following form:

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

    The collection has the following indexes:

    { user: 1 }
    
    { "user.login": 1 }
    

    The { user: 1 } index covers the following query:

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

    However, the { "user.login": 1 } index does not cover the following query:

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

    The query, however, does use the { "user.login": 1 } index to find matching documents.

To determine whether a query is a covered query, use the explain() method. If the explain() output displays true for the indexOnly field, the query is covered by an index, and MongoDB queries only that index to match the query and return the results.

For more information see Measure Index Use.