MongoDB : Indexes order and query order must match?

Question

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.

Answer 1

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).

Answer 2

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.