Compound Indexes

MongoDB supports compound indexes, where a single index structureholds references to multiple fields [1]within a collection’s documents. The following diagram illustrates anexample of a compound index on two fields:

Compound indexes can support queries that match on multiple fields.

Create a Compound Index

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

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

The value of the field in the index specification describes the kind ofindex for that field. For example, a value of 1 specifies an indexthat orders items in ascending order. A value of -1 specifies anindex that orders items in descending order. For additional indextypes, see index types.

Important

You may not create compound indexes that havehashed index type. You will receive an error if you attempt tocreate a compound index that includes a hashed indexfield.

Consider a collection named products that holds documents thatresemble 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 andstock fields:

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

The order of the fields listed in a compound index is important. Theindex will contain references to documents sorted first by the valuesof the item field and, within each value of the item field, sorted byvalues of the stock field. See Sort Orderfor 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 theindex fields. That is, the index supports queries on the item fieldas 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) ordescending (-1) sort order. For single-field indexes, the sortorder of keys doesn’t matter because MongoDB can traverse the index ineither direction. However, for compound indexes, sort order can matter in determining whetherthe index can support a sort operation.

Consider a collection events that contains documents with thefields username and date. Applications can issue queries thatreturn results sorted first by ascending username values and thenby 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 usernamevalues 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 ascendingusername values and then by ascending date values, such as thefollowing:

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

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

Prefixes

Index prefixes are the beginning subsets of indexed fields. Forexample, 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 onthe index prefixes. As such, MongoDB can use the index for queries onthe 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 item andstock fields since item field corresponds to a prefix. However,the index would not be as efficient in supporting the query as would bean index on only item and stock.

However, MongoDB cannot use the index to support queries that includethe following fields since without the item field, none of thelisted 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 onits prefix (e.g. { a: 1, b: 1 } and { a: 1 }), if neither indexhas a sparse or unique constraint, then you can remove the index on theprefix (e.g. { a: 1 }). MongoDB will use the compound index in allof the situations that it would have used the prefix index.

Index Intersection

Starting in version 2.6, MongoDB can use indexintersection to fulfill queries. The choicebetween creating compound indexes that support your queries or relyingon index intersection depends on the specifics of your system. SeeIndex Intersection and Compound Indexes for more details.

Additional Considerations

Applications may encounter reduced performance during indexbuilds, including limited read/write access to the collection. Formore information on the index build process, seeIndex Builds on Populated Collections, including theIndex Builds in Replicated Environments section.

Some drivers may specify indexes, using NumberLong(1) rather than1 as the specification. This does not have any affect on theresulting index.