A composite index, called a compound index in MongoDB, has a key made from multiple document fields. Use one when recurring queries filter or sort on those fields together. Field order matters: an index on year, type, and IMDb rating can support queries starting with year, but not queries on type or rating alone. section 7.2.2
Listing 7.6 Creating a compound index on three fields
db.movies.createIndex(
{ year: 1, type: 1, "imdb.rating": 1 }
)
Create a three-field index for movie searches with db.movies.createIndex({ year: 1, type: 1, "imdb.rating": 1 }). Each 1 specifies ascending order. section 7.2.2
Check the leading fields before relying on that index:
- A query on year; year and type; or all three fields matches an index prefix. section 7.2.2
- A query on type or IMDb rating without year does not match a prefix of this index. In the illustrated rating-only query, explain("executionStats") reports a COLLSCAN examining 21,349 documents. section 7.2.2
Index a filter and sort together
For db.movies.find({ year: 1914, "imdb.rating": { $gte: 7 } }).sort({ title: 1 }), create db.movies.createIndex({ year: 1, title: 1, "imdb.rating": 1 }). This places the year filter first, the title sort next, and the rating condition last. The illustrated explain plan uses IXSCAN followed by FETCH; it examines four index keys and returns two documents. section 7.2.2
Compound indexes also support multi-field sorting when sort keys follow their index order: { runtime: 1, year: 1 } supports sorting by runtime and then year, not the reverse. Choose field order from the queries and sorts the application actually runs, then inspect the execution plan rather than assuming an index is used. section 7.6.7 section 7.2.2
