Search code examples
mongodbmongodb-querymongodb-indexes

Mongodb query - Does sequence of key matters in usage of compound index

Suppse I created index like following :-

db.collection.createIndex( { propA: 1, propB: 1, propC:1 } )

and I query like following :-

db.collection.find({propB:'x', propC: 'y', propA:'z'})

will mongo query engine use the index created above or not. Does sequence of key matters in usage of compound index while writing query?

Solution

  • The order of keys in a query doesn't matter: MongoDB is smart enough to look at all the queried properties and find a suitable index.

    However, the order of keys defined in an index does matter: a compound index can be used to match queries against any prefix of its keys, in the order they are defined in the index document. So your index above can be used to answer queries like {propA: 'x', propB: 'y'} but not queries like {propB: 'y', propC: 'z'}.

    You can use explain() to figure out which index MongoDB is going to use for a specific query.