CODEWITHSUNDEEP
mongodbsortingfiltermongodb-querymongodb-indexes
LuFFy
LuFFy

Reputation: 9297

Both Filtering & Sorting Which of following queries will use index?

There is a collection people with the following index:

{"first_name": 1, "address.state": -1, "address.city": -1, "ssn": 1}

For Both Filtering & Sorting, which of the following queries will use index ?

  1. { "first_name": { $gt: "J" } }).sort({ "address.city": -1 }
  2. { "first_name": "Jessica" }).sort({ "address.state": 1, "address.city": 1 }
  3. { "first_name": "Jessica", "address.state": { $lt: "S"} }).sort({ "address.state": 1 }
  4. {"address.city":"West Cindy"}).sort({ "address.city":-1}
  5. {"address.state":"South Dakota","first_name": "Jessica"}).sort({ "address.city":-1}

I already Went through following Question : Which of the following queries will use the index?

But it only explains the indexes for filtering, I need to use index for both Filters as well as Sort functionality.

Also, How Do I Determine whether Index is used for Both Filter & Sort OR Not used ?

Upvotes: 5

Views: 8962

Answers (1)

love gupta
love gupta

Reputation: 529

Mongo uses index from Left i.e. {"first_name": 1, "address.state": -1, "address.city": -1, "ssn": 1} index can be applied on below field queries-

  • {"first_name": 1, "address.state": -1, "address.city": -1, "ssn": 1}
  • {"first_name": 1, "address.state": -1, "address.city": -1}
  • {"first_name": 1, "address.state": -1}
  • {"first_name": 1}

One should also note that order is important for compound index.

Coming to the question asked, I know that it is the assignment question for M201 course Lab 2.1 so I am well aware of data set. I will go with the options one by one-

  1. { "first_name": { $gt: "J" } }).sort({ "address.city": -1 } It can't be the option because sort is on address city so index can't be used out of order.

  2. { "first_name": "Jessica" }).sort({ "address.state": 1, "address.city": 1 } It can be an option. To ensure it, we need to run below query-

    var ex = db.people.explain();
ex.find({ "first_name": "Jessica" }).sort({ "address.state": 1, "address.city": 1 })

Above query returns a response which doesn't have something like "stage" : "SORT" which tells us that sort happened in DB using index. If we have Stage SORT then it tells that sorting happened in RAM and DB was unable to do it in database using index.

  1. { "first_name": "Jessica", "address.state": { $lt: "S"} }).sort({ "address.state": 1 } I did same as option 2.

    ex.find({ "first_name": "Jessica", "address.state": { $lt: "S"} }).sort({ "address.state": 1 }) Above output doesn't has any SORT stage which shows that DB was able to use index for sorting.

  2. {"address.city":"West Cindy"}).sort({ "address.city":-1} Ignore this because index is not from left.

  3. {"address.state":"South Dakota","first_name": "Jessica"}).sort({ "address.city":-1} This is same as option 2. I executed the similar query and didn't get any SORT stage so it uses index for sorting.

Using index for filtering is very easy to identify. If ex.find(<Your query>) is giving a "stage" : "COLLSCAN" then index was not used for filtering. Option 2, 3, 5 doesn't have "stage" : "COLLSCAN" in ex.find() response so these uses index for filtering.

This way I ensured which all options use index for both filtering and sorting.

You can also run ex.find() for option 1 and 4 and you will get "stage" : "COLLSCAN" or "stage" : "SORT" which tells that index was not used for filtering or sorting respectively.

Thanks ...

Upvotes: 5

Related Questions