Can MongoDB use fields after a range condition in a compound index?

Working with Data
, ,
1

I’m trying to understand how MongoDB query planning works with compound indexes.

Suppose I have the following index:

db.users.createIndex({ city: 1, age: 1, intention: 1 });

And the following queries:

Case A (range in the middle):

db.users.find({
  city: "Chicago",
  age: { $gte: 25, $lte: 35 },
  intention: "travel"
})

Case B ($in instead of range):

db.users.find({
  city: "Chicago",
  age: { $in: [25,26,27,28,29,30,31,32,33,34,35] },
  intention: "travel"
})

From what I’ve read:

  • In Case A, MongoDB can use city and the age range, but it won’t use intention in the index. It will instead filter intention in memory after scanning.

  • In Case B, MongoDB can issue multiple point seeks into the index (for each age value), so it can use intention in the index.

  1. Can someone confirm that the above are true?
  2. And if the above are true, then if I have such query and compound index, is Case B the most efficient?