- Indexes >
- Index Concepts >
- Index Types >
- Compound Indexes
Compound Indexes¶
On this page
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:
[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:
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:
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:
or queries that return results sorted first by descending username
values and then by ascending date
values, such as:
The following index can support both these sort operations:
However, the above index cannot support sorting by ascending
username
values and then by ascending date
values, such as the
following:
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.
Example
Given the following index:
MongoDB can use this index to support queries that include:
- the
item
field, - the
item
field and thelocation
field, - the
item
field and thelocation
field and thestock
field, or - only the
item
andstock
fields; however, this index would be less efficient than an index on onlyitem
andstock
.
MongoDB cannot use this index to support queries that include:
- only the
location
field, - only the
stock
field, or - only the
location
andstock
fields.