Index prefix m201

Could you please me to understand what’s difference in these 2 queries
db.test.explain().find({a:1,b:3}).sort({b:1}) - why this is not index scan
db.test.explain().find({a:1,b:3}).sort({a:1}) - Index scan - understand
Collection test as
{ “_id” : 1, “a” : 5, “b” : 3, “c” : 4 }

Hi!

What is the index you created? Can you include it in the post?

My bad :frowning_man: here we go !
{a:1,b:1,c:1}

I’ll give you some opinions more than an answer.

As they are written:

  1. Both queries will fetch and do an index scan.
  2. To avoid index scan, the query should point to a single index document (didn’t test this).

1 will be covered (no fetching/reading from original DB) if we do

db.test.explain().find({a:1,b:3}, {_id:0, a:1, b:1, c:1}).sort({a:1})

Indexes collection is easier to traverse, and it’s on RAM, so this is a good case scenario for queries.

Observe there are no non-indexed fields in find, and we project only indexed fields.

An interesting thing appears if you test

db.test.explain().find({a:1,b:3}, {_id:0, a:1, b:1, c:1}).sort({b:1})

Because a covered query is rejected (this only has to do with fetch, as discussed above.)

Mongo calculates the works for each query plan, sometimes it rejects a good one. We can force plans to be executed though.

Again, not an answer, just some ideas.