/ /

Create a Compound Index

Compound indexes are indexes that contain references to multiple fields. Compound indexes improve performance for queries on exactly the fields in the index or fields in the index prefix.

To create a compound index, use the db.collection.createIndex() method:

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

Restriction

You can specify up to 32 fields in a single compound index.

Before You Begin

The examples on this page use data from the sample_mflix dataset. For details on how to load this dataset into your self-managed MongoDB deployment, see Load the Sample Dataset. If you made any modifications to the sample databases, you may need to drop and recreate the databases to run the examples on this page.

Procedure

The following operation creates a compound index containing the title and metacritic fields:

db.movies.createIndex( { title: 1, metacritic: -1 } )

title_1_metacritic_-1

In this example:

The index on title is ascending (1).
The index on metacritic is descending (-1).

Results

The created index supports queries that select on:

Both title and metacritic fields.
Only the title field, because title is a prefix of the compound index.

For example, the index supports these queries:

db.movies.find( { title: "The Godfather", metacritic: 100 }, { _id: 0, title: 1 } )

[
  {
    title: 'The Godfather'
  }
]

db.movies.find( { title: "The Godfather" }, { _id: 0, title: 1 } )

[
  {
    title: 'The Godfather'
  }
]

The index does not support queries on only the metacritic field, because metacritic is not part of the index prefix. For example, the index does not support this query:

db.movies.find( { metacritic: { $gt: 98 } }, { _id: 0, title: 1, metacritic: 1 } )

[
  {
    metacritic: 100,
    title: 'The Wizard of Oz'
  },
  {
    metacritic: 99,
    title: 'The Night of the Hunter'
  },
  {
    metacritic: 100,
    title: 'Lawrence of Arabia'
  },
  {
    metacritic: 100,
    title: 'The Leopard'
  },
  {
    metacritic: 100,
    title: 'The Conformist'
  },
  {
    title: 'The Godfather',
    metacritic: 100
  },
  {
    metacritic: 100,
    title: 'Fanny and Alexander'
  },
  {
    metacritic: 100,
    title: 'Boyhood'
  },
  {
    metacritic: 100,
    title: 'Best Kept Secret'
  }
]

Learn More

To learn how to create efficient compound indexes, see The ESR (Equality, Sort, Range) Guideline.
To learn how sort order (ascending or descending) impacts performance of compound indexes, see Use Indexes to Sort Query Results.
To learn about other index types, see Index Types.
To learn what properties you can specify for indexes, see Index Properties.

Back

Compound

Sort Order