2

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?

Anand
  • 4,523
  • 10
  • 47
  • 72

2 Answers2

2

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.

Avish
  • 4,516
  • 19
  • 28
  • 1
    So for 100% clarity `db.collection.find({propB: 'y', propA: 'x'})` will also use the index. Understanding this was key for me. – John Mark Scarborough Jun 02 '19 at 17:18
  • Yes, that is correct: since `propA` + `propB` form a prefix of the indexed properties (`propA, propB, propC`), then using just those in a query - in any order - will still use the index. – Avish Jun 03 '19 at 19:57
  • @SindhooOad `propA, propC` is not a prefix of the indexed properties; but `propA` is. So querying on `propA` and `propC` together will make MongoDB use `propA` to query the index, then filter the results according to `propC` without using the index. But why not try it with `explain()` to see for yourself? – Avish Jul 23 '20 at 10:38
  • @avish I have a index `{ "v" : 2, "key" : {"is_pending" : 1, "r_id" : 1, "type" : 1 }, "name" : "is_pending_1_r_id_1_type_1"}` when I ran this query `.find({"is_pending":true,"type" : "signup"}).explain()` got this in winningPlan `"indexName" : "is_pending_1_r_id_1_type_1", `. So it is running that index on above query on both keys. – Sindhoo Oad Sep 29 '20 at 06:04
  • 1
    @SindhooOad my understanding is that it will only be using the index for the `is_pending` key and not for `type`, because `r_id` isn't included in the query and appears in the index before `type`. You should be able to see which keys are being used during the index scan in the `keyPattern` part of the explain results. See here for more information: https://docs.mongodb.com/manual/tutorial/analyze-query-plan/ – Avish Oct 02 '20 at 10:08
1

To add to Avish's answer, such an index comprising of more than one fields is called a compound index. The order of the fields listed in a compound index is quite important. Here is why:

The index will contain references to documents sorted first by the values of the first field and, within each value of the first field, sorted by values of the second field and, within each value of the second field, sorted by values of the third field, and so on.

For example in your case, index will contain references to documents sorted first by the values of the propA field and, within each value of the propA field, sorted by values of the propB field and, within each value of the propB field, sorted by values of the propC field.

So keep in mind that while following queries will use the given index:

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

The following can't use the given index:

  • db.collection.find({propB:'x'})
  • db.collection.find({propC:'y'})
  • db.collection.find({propB:'x', propC: 'y'})
him_j
  • 31
  • 6