0

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});
user1955934
  • 3,185
  • 5
  • 42
  • 68
  • What field are you trying to sort on? – prasad_ Jan 25 '20 at 09:08
  • the rating is always provided for sorting – user1955934 Jan 25 '20 at 10:32
  • 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](https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/index.html#sort-and-index-prefix). – prasad_ Jan 25 '20 at 10:57
  • Does this answer your question? [MongoDB Find performance: single compound index VS two single field indexes](https://stackoverflow.com/questions/47893613/mongodb-find-performance-single-compound-index-vs-two-single-field-indexes) – Valijon Jan 25 '20 at 16:40

1 Answers1

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});
Pavel Bely
  • 2,245
  • 1
  • 16
  • 24