MongoDB Compound Index Prefix with Sort

Question

I have trouble deciding where to best put sorting field in a mongodb compound index.

My understanding is that we need to choose the highest cardinality & selectivity as the preceeding fields in a compound index

db.person.createIndex({ rating: 1, name: 1, gender: 1});

For this example, rating only ranges from 1 to 5 and gender is M or F So, name is always the highest cardinality & selectivity and should be the leftmost field in the compound index; however, the use case for query is such that rating is always provided in the query, and either name or gender can optionally be provided via filter in the application.

Which option is best for this case:

1)

db.person.createIndex({ rating: 1, name: 1, gender: 1});

2)

db.person.createIndex({ rating: 1});
db.person.createIndex({ name: 1, gender: 1});
db.person.createIndex({ gender: 1});

It is difficult to say anything without actually trying some real queries and sample datasets with different indexes. In general, since the rating is used for query filter and also for sorting always, the index { rating: 1, name: 1, gender: 1} can be considered as the main candidate. Query plans (using explain) will provide sufficient info to make a decision. Also, see MongoDB docs topics on using compound indexes for sorting: Use Indexes to Sort Query Results. — prasad_
Does this answer your question? MongoDB Find performance: single compound index VS two single field indexes — Valijon

Pavel Bely Pavel Bely · Accepted Answer · 2021-02-13T04:50:04

You need to add these indexes:

// supports query with rating and gender specified
db.person.createIndex({ rating: 1, gender: 1});

// supports query with rating and name (and optionally gender) specified
db.person.createIndex({ name: 1, rating: 1, gender: 1});

MongoDB Compound Index Prefix with Sort

1 Answers