Docs Menu

Docs HomeMongoDB Manual

Compound Indexes

On this page

  • Use Cases
  • Get Started
  • Details
  • Field Limit
  • Field Order
  • Sort Order
  • Hashed Index Fields
  • Index Prefixes
  • Sparse Compound Indexes
  • Learn More

Compound indexes collect and sort data from two or more fields in each document in a collection. Data is grouped by the first field in the index and then by each subsequent field.

For example, the following image shows a compound index where documents are first grouped by userid in ascending order (alphabetically). Then, the scores for each userid are sorted in descending order:

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.

Indexing commonly queried fields increases the likelihood of covered queries. A covered query is a query that can be satisfied entirely using an index and does not have to examine any documents, leading to greatly improved performance.

To create a compound index, use the following prototype:

db.<collection>.createIndex( {
<field1>: <sortOrder>,
<field2>: <sortOrder>,
...
<fieldN>: <sortOrder>
} )

If your application repeatedly runs a query that contains multiple fields, you can create a compound index to improve performance for that query. For example, a grocery store manager often needs to look up inventory items by name and quantity to determine which items are low stock. You can create a compound index on both the item and quantity fields to improve query performance.

A compound index on commonly queried fields increases the chances of covering those queries. Covered queries are queries that can be satisfied entirely using an index, without examining any documents. This optimizes query performance.

To create a compound index, see Create a Compound Index.

This section describes technical details and limitations for compound indexes.

A single compound index can contain up to 32 fields.

The order of the indexed fields impacts the effectiveness of a compound index. Compound indexes contain references to documents according to the order of the fields in the index. To create efficient compound indexes, follow the ESR (Equality, Sort, Range) rule.

Indexes store references to fields in either ascending (1) or descending (-1) sort order. For compound indexes, sort order can determine whether the index supports a sort operation. For more information, see Compound Index Sort Order.

  • In MongoDB 4.4 and later, compound indexes may contain a single hashed index field.

  • In MongoDB 4.2 and earlier, compound indexes cannot contain any hashed index fields.

Index prefixes are the beginning subsets of indexed fields. Compound indexes support queries on all fields included in the index prefix.

For example, consider this compound index:

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

The index has these index prefixes:

  • { item: 1 }

  • { item: 1, location: 1 }

MongoDB can use the compound index to support queries on these field combinations:

  • item

  • item and location

  • item, location, and stock

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, only the item field in the index can support this query. The query cannot use the stock field which follows location.

Index fields are parsed in order; if a query omits an index prefix, it is unable to use any index fields that follow that prefix.

MongoDB cannot use the compound index to support queries on these field combinations:

  • location

  • stock

  • location and stock

Without the item field, none of the preceding field combinations correspond to a prefix index.

Tip

Remove Redundant Indexes

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

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:

Compound Index Components
Compound Index Behavior
Ascending indexes
Descending indexes
Only indexes documents that contain a value for at least one of the keys.
Ascending indexes
Descending indexes
Only indexes a document when it contains a value for one of the geospatial fields. Does not index documents in the ascending or descending indexes.
Ascending indexes
Descending indexes
Only indexes a document when it matches one of the text fields. Does not index documents in the ascending or descending indexes.

To learn how to create efficient compound indexes, see The ESR (Equality, Sort, Range) Rule.

←  Create an Index on a Single FieldCreate a Compound Index →