2

Ordering in compound indexes matter. But does the ordering in reads/writes matter? Is mongodb smart enough to reorder the fields based on indexes available?

The code is in python. I read that it is preferable to pass in ordered dictionary using: http://api.mongodb.org/python/current/api/bson/son.html.

But after switching between both ordered and unordered dictionaries, I don't see any difference in performance. Running explain on either options, showed that it evaluated different indexes (allPlans field) and did pick up the index whose order was different from the way it received the query.

Would love if this was confirmed either way.

I am using mongo 2.6. May be mongo added some kind of optimization in their newer versions.

Edit: (added example) For ex:

Index is on {c: 1, a: 1, b: 1}
Intended query: db.tble.find(some_dict)

Which one is better? (in python)

  1. some_dict = {c: "C", a: "A", b: "B"} # unordered.
  2. some_dict = bson.son.SON({c: "C", a: "A", b: "B"})

Neither made any difference but it is recommended to use 2nd, I don't know why.

shrnkrn
  • 121
  • 1
  • 9
  • 3
    Field order doesn't matter in a query object. I assume it would be considered a bug if it did as it has no bearing on the meaning of the query. Is that what you mean by "ordering in reads/writes"? – JohnnyHK Sep 20 '14 at 02:16
  • Yeah field order. Something like: db.table.find({"a": A, "b": B}) – shrnkrn Sep 20 '14 at 15:47
  • @JohnnyHK edited my question with an example. Hope it's clearer now. – shrnkrn Sep 20 '14 at 16:02

1 Answers1

0

The order in which you specify fields in a query, e.g.

db.collection.find({ "a" : 2, "b" : 4})

vs.

db.collection.find({ "b" : 2, "a" : 4})

doesn't matter, as there's no semantic difference between the two queries. The order of fields matters when specifying compound indexes, e.g.

db.collection.ensureIndex({ "a" : 1, "b" : 1})

vs.

db.collection.ensureIndex({ "b" : 1, "a" : 1})

because a compound index can only be used to satisfy queries on a prefix of the ordered index keys. This means that the former index on { "a" : 1, "b" : 1} can satisfy the query

db.collection.find({ "a" : 33})

but the latter index on { "b" : 1, "a" : 1} cannot. See the compound index docs for more explanation and examples.

wdberkeley
  • 11,531
  • 1
  • 28
  • 23
  • Yeah you are right. It makes sense for it to not matter but here in python's mongo api (http://api.mongodb.org/python/current/api/bson/son.html), it states: "Regular dictionaries can be used instead of SON objects, but not when the order of keys is important.." I assumed it was talking about indexes and such. When is the order of keys important then? – shrnkrn Sep 22 '14 at 17:55
  • That's a python-specific thing because python dicts do not maintain any order on the keys. – wdberkeley Sep 22 '14 at 18:28
  • yeah well that still does not answer when ordered dictionaries matter. I guess it's important for users when data inserted/updated needs to be in a certain order depending on the application. Other than I can't think of any other reason. Thank you for your answers! – shrnkrn Sep 23 '14 at 18:50
  • Ordered dictionaries matter if you're using a compound document key. – Rodrigo Taboada Oct 31 '14 at 15:35