Order of the fields in the query predicate

Hi,

A bit confused on the comment “The order of the fields in the query predicate does not matter”. Isn’t the indexes built on BTree structure so the order does matter ?

For example the compound index :
db.people.createIndex( { “first_name”: 1, “address.state”: -1, “address.city”: -1, “ssn”: 1 } )

exp.find({ “first_name”: “Jessica”, “address.state”: “South Dakota”}) and
exp.find({ “address.state”: “South Dakota”, “first_name”: “Jessica”}) has the same output, key match etc.

Does the both query match on the first_name as the first index and address.state second ?

Hey @David_60430

This is correct. However order matters for creating the index. Not for the fields in the query predicate. The query is a JS object which mongo will AND together and arrange to match up with the best index to use.

Order does matter for when you start to introduce sort() and are trying to use the indexes. See the docs below
https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/index.html

So given the index you have created.

The following queries will use it.

db.people.find({first_name: 'Bob', 'address.state': 'NY'})
db.people.find({'address.state': 'NY', 'address.city': 'NYC', first_name: 'Joe'})
db.people.find({first_name: 'Sasha'}).sort({'address.state': 1})

However the following will not use the index

db.people.find({'address.state': 'NY', 'address.city'; 'NYC' })
1 Like

Thanks, that is very interesting.

Are there rules or guidelines on how indexes will be used in queries ? In the example above, I would not have guessed address.state and address.city alone would not use the index.

db.people.find({‘address.state’: ‘NY’, ‘address.city’; ‘NYC’ })

Guess the query is smart enough to find a way to traverse the index btree with the available fields.

Hey @David_60430

Here are the docs on Compound indexes which do a better job then me at explanation.
https://docs.mongodb.com/manual/core/index-compound/#compound-indexes

This is about index prefixes
https://docs.mongodb.com/manual/core/index-compound/#prefixes

Mongo can do some cool thing with index intersection also, shown here
https://docs.mongodb.com/manual/core/index-intersection/#index-intersection-and-compound-indexes

The following are equivalent to mongodb

db.people.find({ first_name: 'Sean', last_name: 'Campbell' })
db.people.find({ last_name: 'Campbell', first_name: 'Sean' }) 

Which is the same as with JavaScript say there is a function which takes an object as an argument

function sayHello (toObject) {
  const { first_name, last_name } = toObject;
  return `Hello ${first_name} ${last_name}`
}

Which will work when either of the following

sayHello({ first_name: 'Sean', last_name: 'Campbell' })
sayHello({ last_name: 'Campbell', first_name: 'Sean' })