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_
– prasad_, Commented Jan 25, 2020 at 10:57
Does this answer your question? MongoDB Find performance: single compound index VS two single field indexes — Valijon
– Valijon, Commented Jan 25, 2020 at 16:40

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

0

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});

answered Feb 13, 2021 at 4:50

Pavel Bely

2,4751 gold badge20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB Compound Index Prefix with Sort

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related