-
Type:
Bug
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: 2.0.2
-
Component/s: Querying
-
Labels:None
-
Environment:Windows
-
ALL
PRIMARY> db.try2.ensureIndex({"ext.s":1,"ext.k":1})
PRIMARY> db.try2.insert({"ext":[{"s":"s1","k":"key1"},{"s":"s2","k":"key1"}]})
PRIMARY> db.try2.insert({"ext":[{"s":"s1","k":"key2"}]})
PRIMARY> db.try2.find({"ext":{$elemMatch:{"s":"s1","k":"key1"}}}).explain()
{
"cursor" : "BtreeCursor ext.s_1_ext.k_1",
"nscanned" : 2,
"nscannedObjects" : 2,
"n" : 1,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : true,
"indexOnly" : false,
"indexBounds" : {
"ext.s" : [
[
"s1",
"s1"
]
],
"ext.k" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
}
The query works, but it only uses the first key in the compound index, when it should be able to use both. It therefore scans both documents with "ext.s":"s1", instead of just the one matching document.
Note that without multi-keys (if all inserted documents have a single element in the array), both keys will be used.
I believe the use case here is fairly common. In our case, our documents can be associated with identifiers from 3rd party systems. We model each identifier as a triplet of properties (foreign-system-name,foreign-system-id-version,id). We'd like to query for documents using the combination of all 3 properties (in an $elemMatch fashion), but this won't make full use of the index.
Our alternatives for now are to concatenate the sub-properties into a single property or to order the keys in the index to ensure low cardinality. Both options aren't very pretty and have some limitations.
- duplicates
-
SERVER-3104 index bound improvements for elemMatch query on multikey index
-
- Closed
-