Compound Indexes

Compatibility

You can use compound indexes for deployments hosted in MongoDB Atlas.

To learn more about managing indexes for deployments hosted in MongoDB Atlas, see Create, View, Drop, and Hide Indexes.

Create a Compound Index

To create a compound index use an operation that resembles the following prototype:

db.collection.createIndex( { <field1>: <type>, <field2>: <type2>, ... } )

The order of the indexed fields has a strong impact on the effectiveness of a particular index for a given query. For most compound indexes, following the ESR (Equality, Sort, Range) rule helps to create efficient indexes.

Consider a collection named products that holds documents that resemble the following document:

{
 "_id": ObjectId(...),
 "item": "Banana",
 "category": ["food", "produce", "grocery"],
 "location": "4th Street Store",
 "stock": 4,
 "type": "cases"
}

The following operation creates an ascending index on the item and stock fields:

db.products.createIndex( { "item": 1, "stock": 1 } )

The order of the fields listed in a compound index is important. The index will contain references to documents sorted first by the values of the item field and, within each value of the item field, sorted by values of the stock field. See Sort Order for more information.

In addition to supporting queries that match on all the index fields, compound indexes can support queries that match on the prefix of the index fields. That is, the index supports queries on the item field as well as both item and stock fields:

db.products.find( { item: "Banana" } )
db.products.find( { item: "Banana", stock: { $gt: 5 } } )

For details, see Prefixes.

Sort Order

Indexes store references to fields in either ascending (1) or descending (-1) sort order. For single-field indexes, the sort order of keys doesn't matter because MongoDB can traverse the index in either direction. However, for compound indexes, sort order can matter in determining whether the index can support a sort operation.

Consider a collection events that contains documents with the fields username and date. Applications can issue queries that return results sorted first by ascending username values and then by descending (i.e. more recent to last) date values, such as:

db.events.find().sort( { username: 1, date: -1 } )

or queries that return results sorted first by descending username values and then by ascending date values, such as:

db.events.find().sort( { username: -1, date: 1 } )

The following index can support both these sort operations:

db.events.createIndex( { "username" : 1, "date" : -1 } )

However, the above index cannot support sorting by ascending username values and then by ascending date values, such as the following:

db.events.find().sort( { username: 1, date: 1 } )

For more information on sort order and compound indexes, see Use Indexes to Sort Query Results.

Prefixes

Index prefixes are the beginning subsets of indexed fields. For example, consider the following compound index:

{ "item": 1, "location": 1, "stock": 1 }

The index has the following index prefixes:

• { item: 1 }

• { item: 1, location: 1 }

For a compound index, MongoDB can use the index to support queries on the index prefixes. As such, MongoDB can use the index for queries on the following fields:

• the item field,

• the item field and the location field,

• the item field and the location field and the stock field.

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, in this case the index would not be as efficient in supporting the query as it would be if the index were on only item and stock.

Since a query on item and stock omits the location index prefix, it cannot use the stock index field which follows location. Only the item field in the index can support this query. See Create Indexes to Support Your Queries for more information. The index:

1. Matches keys for item.

2. Performs a filter within the IXSCAN stage on the stock field.

3. Returns the filtered result.

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 matching "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.

MongoDB cannot use the index to support queries that include the following fields since without the item field, none of the listed fields correspond to a prefix index:

• the location field,

• the stock field, or

• the location and stock fields.

If you have a collection that has both a compound index and an index on its prefix (e.g. { a: 1, b: 1 } and { a: 1 }), if neither index has a sparse or unique constraint, then you can remove the index on the prefix (e.g. { a: 1 }). MongoDB will use the compound index in all of the situations that it would have used the prefix index.

Index Intersection

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.

Sparse Compound Indexes

Compound indexes can contain different types of sparse indexes. The combination of index types determines how the compound index matches documents.

This table summarizes the behavior of a compound index that contains different types of sparse indexes:

Additional Considerations

During index builds, applications may encounter reduced performance or limited read/write access to the collection being indexed.

For more information on the index build process, see Index Builds on Populated Collections, especially the Index Builds in Replicated Environments section.

Some drivers use NumberLong(1) instead of 1 to specify the index order. The resulting indexes are the same.