OPTIONS

Compound Indexes

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

Diagram of a compound index on the ``userid`` field (ascending) and the ``score`` field (descending). The index sorts first by the ``userid`` field and then by the ``score`` field.
[1]MongoDB imposes a limit of 31 fields for any compound index.

Compound indexes can support queries that match on multiple fields.

Example

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",
 "arrival": Date(...)
}

If applications query on the item field as well as query on both the item field and the stock field, you can specify a single compound index to support both of these queries:

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

Important

You may not create compound indexes that have hashed index fields. You will receive an error if you attempt to create a compound index that includes a hashed index.

The order of the fields in a compound index is very important. In the previous example, 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. 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.ensureIndex( { "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 } )

Prefixes

Compound indexes support queries on any prefix of the index fields. Index prefixes are the beginning subset of indexed fields. For example, given the index { a: 1, b: 1, c: 1 }, both { a: 1 } and { a: 1, b: 1 } are prefixes of the index.

If you have a collection that has a compound index on { a: 1, b: 1 }, as well as an index that consists of the prefix of that index, i.e. { a: 1 }, assuming none of the index has a sparse or unique constraints, then you can drop the { a: 1 } index. MongoDB will be able to use the compound index in all of situations that it would have used the { a: 1 } index.

For example, given the following index:

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

MongoDB can use this index to support queries that include:

  • the item field,
  • the item field and the location field,
  • the item field and the location field and the stock field, or
  • only the item and stock fields; however, this index would be less efficient than an index on only item and stock.

MongoDB cannot use this index to support queries that include:

  • only the location field,
  • only the stock field, or
  • only the location and stock fields.

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.