Index not used depending on query formulation

Working with Data
,
1

We see in one of our services that certain queries always result in a COLLSCAN, instead of the appropriate index being used. The data has the form

{_id: DocumentID, "field": {"child": "Cross-App-ID"}}

with an Index {"field.child":1}.

Running the query for “field.child”, i.e. db.collection.find({"field.child":"CA-ID"}) works as expected with the index being used. However, for some reason the app accessing the database creates the queries as db.collection.find({"field":{"child":"CA-ID"}}), which results in a COLLSCAN instead of an IXSCAN.

2 questions for me from this:

  • Shouldn’t these queries be equivalent and thus treated the same?
  • How can I create an index for the second use-case? All examples I found create the index like we already did.
2

No they are NOT equivalent. The query parent.child:value means match the field child of the object parent to be equal to the value value. While the query parent : {child:value} means match the object parent to be equal to the object { child:value}.

And the later will not match parent:{child:value,name:steevej}. It will only match object that have the same fields, in the same orders and each field with the same value.

There is no specific examples because there is no difference. For queries parent:{child:value} you need an index on the field parent. The key will be {child:value}.

3

@Peter_Ludikovsky, please provide followup on my replies. I think it explains well the issue.

Thanks