27

This question concern the internal method to manage indexes and serching Bson Documents.

When you create a multiple indexes like "index1", "index2", "index3"...the index are stored to be used during queries, but what about the order of queries and the performance resulting.

sample
index1,index2,index3----> query in the same order index1,index2,index3 (best case) index1,index2,index3----> query in another order index2,index1,index3 (the order altered)

Many times you use nested queries including these 3 index and others items or more indexes. The order of the queries would implicate some time lost?. Must passing the queries respecting the indexes order defined or the internal architecture take care about this order search? I searching to know if i do take care about this or can make my queries in freedom manier.

Thanks.

Scott Hernandez
  • 7,452
  • 2
  • 34
  • 25
user325558
  • 1,413
  • 5
  • 22
  • 35

2 Answers2

50

The order of the conditions in your query does not affect whether it can use an index or no.

e.g. typical document structure:

{
    "FieldA" : "A",
    "FieldB" : "B"
}

If you have an compound index on A and B :

db.MyCollection.ensureIndex({FieldA : 1, FieldB : 1})

Then both of the following queries will be able to use that index:

db.MyCollection.find({FieldA : "A", FieldB : "B"})
db.MyCollection.find({FieldB : "B", FieldA : "A"})

So the ordering of the conditions in the query do not prevent the index being used - which I think is the question you are asking.

You can easily test this out by trying the 2 queries in the shell and adding .explain() after the find. I just did this to confirm, and they both showed that the compound index was used.

however, if you run the following query, this will NOT use the index as FieldA is not being queried on:

db.MyCollection.find({FieldB : "B"})

So it's the ordering of the fields in the index that defines whether it can be used by a query and not the ordering of the fields in the query itself (this was what Lucas was referring to).

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • 6
    The important thing to note here is the order in the query doesn't matter, but the order of the fields defined in the index does. – Scott Hernandez Mar 09 '11 at 18:01
  • 1
    But how do you specify the order when defining the index? ensureIndex takes a JS object as a param, and the fields JS objects are not ordered. (Hi Scott!) – Leopd Sep 23 '11 at 23:52
  • 4
    @Leopd: They're not ordered per spec, but they "happen to be" ordered in implementations, and MongoDB depends on this behavior. It's one of the more unpleasant design warts in MongoDB, and leads to hoop jumping in most other languages (since most dictionary types are *not* ordered). – Glenn Maynard Apr 16 '12 at 13:34
  • Order absolutely matters, verified on a collection here – Cmag Mar 30 '15 at 21:00
  • "The order of the fields in a compound index is very important. In the previous example, the index will contain references to documents sorted first by the values of the item field and, within each value of the item field, sorted by values of the stock field. See Sort Order for more information." – Cmag Mar 30 '15 at 21:00
  • 2
    @Cmag - to clarify, as it sounds like you have downvoted this answer and have therefore misunderstood what I was saying. The order of the fields in the index *does* matter - we agree and that is per my answer (final sentence in my answer). However, given a compound index, the order of the conditions in a query *does not* matter, as per my example – AdaTheDev Mar 31 '15 at 08:28
  • Furthermore, if you query on `Field A` alone mongoDB _will_ use the index since field A is the prefix of the index – jorgenkg Mar 22 '18 at 09:38
10

From http://www.mongodb.org/display/DOCS/Indexes:

If you have a compound index on multiple fields, you can use it to query on the beginning subset of fields. So if you have an index on

a,b,c

you can use it query on

a

a,b

a,b,c

So yes, order matters. You should clarify your question a bit if you need a more precise answer.

Lucas Zamboulis
  • 2,494
  • 5
  • 24
  • 27
  • 1
    What about b,a or b,c,a or c,b,a. You speak about you can query, but that is know, I demand exclusivelly about the order of the queries that will be presented to the indexer. var quer=Query.AND(Query.EQ("B",b),Query.EQ("A",a)), query=Query.Or(query,Query.EQ("C",c)). The internal will undestand A,B,C or will be lost B,A,C. – user325558 Mar 09 '11 at 12:59
  • By this comment, and assuming an index (A,B,C), I assume you are asking whether the query optimiser will be able to rearrange an example query on (B,C,A) to (A,B,C) in order to use the index. The answer is yes, and this can be easily confirmed by using `explain` (as @AdaTheDev pointed out). Note: at least this is true on 1.8.0rc1 - haven't tested on previous versions. – Lucas Zamboulis Mar 09 '11 at 13:26
  • so does that mean if I am querying only on b and c, the index will not be used? – MonkeyBonkey Mar 28 '12 at 04:44
  • From the page I referenced above: "If the first key is not present in the query, the index will only be used if hinted explicitly." (which applies to MongoDB 1.6+). So it will only be used if you use hint with the query - but not sure you would want to do that, depends on your dataset. – Lucas Zamboulis Mar 28 '12 at 14:01