1

We query a schools collection by two fields using the mongocsharpdriver (v2.0.0) API like:

db.GetCollection<School>("schools").Find(x => x.City == myCity && x.State == myState);

Will the order of the fields listed in the lambda expression generate a query to leverage a compound index with the same order: { "city": 1, "state": 1 }?

I'm fairly new to mongodb, but if I understand correctly, we could create a second (reversed) compound index: { "state": 1, "city": 1 } to ensure one of them is used. I'm just more interested how the lambda is translated and couldn't find what I was looking for in mongodb's documentation.

clueds
  • 13
  • 4

2 Answers2

0

According to this answer, you cannot and should not rely on the ordering of the specific attributes in json. Since this is how MongoDB stores data, this extends to Monogo - there is no way to ensure attribute order, and by the way the data is defined, you shouldn't.

DISCLAIMER This is how Lambdas are consumed for SQL, and only might apply to how MonogDB's drivers work. As for how the lambda statement is consumed, if the method you're using take a parameter of type Expression<TDellegate>, it receives the expression tree instead of the actual method, which means the metadata for the statement youcve made instead of compiled code. This can then be used when delivering your data to translate into the needed queries for the database.

Community
  • 1
  • 1
David
  • 10,458
  • 1
  • 28
  • 40
  • The JSON I posted (`{ "city": 1, "state": 1 }')` is the MongoDB index. If that's our sole index, I'm actually relying (questioning) on the lambda expression to generate a query to leverage that index: `db.schools.find({ "city": "myCity", "state": "myState" })` – clueds May 15 '15 at 13:13
0

The order in which your query is built, whether by typing in json explicitly or generating it from a lambda expression has no effect on the index selection.

When you have a compound index in MongoDB the order of the properties in the index matters but the order in the query doesn't.

Your index, { "city": 1, "state": 1 }, can satisfy both of these queries exactly the same:

db.GetCollection<School>("schools").Find(x => x.City == myCity && x.State == myState);
db.GetCollection<School>("schools").Find(x => x.State == myState && x.City == myCity);

The difference comes when you use a single property so this:

db.GetCollection<School>("schools").Find(x => x.City == myCity);

Works better with your index than this:

db.GetCollection<School>("schools").Find(x => x.State == myState);

More in MongoDB : Indexes order and query order must match?

Community
  • 1
  • 1
i3arnon
  • 113,022
  • 33
  • 324
  • 344
  • 1
    Perfectly clear. I was reading too much into the driver, without fully understanding how mongodb queries natively use indexes. Thanks for your answer! – clueds May 15 '15 at 19:08