Equality Sort Range Rule in M201 course

Hi,

In lesson 4.1 the final query was

db.restaurants.find({"address.zipcode: {$gt: ‘50000’}, cusine: ‘Sushi’, }).sort({stars: -1})

and the index used:

db.restaurants.createIndex({“cusine”: 1, “stars”:1, “address.zipcode”:1})

I understand the equality sort range rule and how it will optimize the query performance.

My question is:
Is the order in find command important? I mean that since the index is cuisine, stars, address.zipcode so the find command must be in the same order or no need for the order as the example above?

Thanks,
Mohamed

Here are some key points re order:

  1. The order of the query predicates does not matter
  2. The order of the sort fields matter
  3. The order of the index keys/prefixes within the compound index matter
2 Likes

Hi @Mohamed_67740,

In addition to @007_jb’s reply, if the fields in the query filter have all equality conditions, then the order will not matter.

There are many use cases to consider for creating effective indexes.
Please refer to the following link to understand indexing strategies:
https://docs.mongodb.com/manual/applications/indexes/

Please feel free to reach out if you have any questions.

Thanks,
Sonali

1 Like

db.restaurants.find({"address.zipcode: {$gt: ‘50000’}, cusine: ‘Sushi’, }).sort({stars: -1})

and the index used:

db.restaurants.createIndex({“cusine”: 1, “stars”:1, “address.zipcode”:1})

I am bit confused as the query predicate doesn’t follow the index prefix rules but still the winning plan is IXSCAN

What rules exactly is the query predicate not following?

I was referring to these rules https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/#sort-on-multiple-fields

There’s only one sort field. I still don’t see it breaking any rules. Have you read mine and @Sonali_Mamgain’s comments?

Hi Ankujgup,

as 007_jb mentioned:
The order of the query predicates doesn’t matter.
The order of the sort matter.

Hi @Ankujgup,

The query uses the index as it follows the ESR rule. For compound indexes, this rule of thumb is helpful in deciding the order of fields in the index:

  • First, add those fields against which Equality queries are run.
  • The next fields to be indexed should reflect the Sort order of the query.
  • The last fields represent the Range of data to be accessed.

Please feel free to reach out if you have any questions.

Thanks,
Sonali