Hello,
I have a MongoDB collection implementing the attribute pattern, which works well most of the time.
However it seems that the order of conditions in the query can lead to slow queries, depending on how the data is distributed.
Given this (simplified) data structure
{
filterAttributes: [
{
k: "booleanAttribute",
v: true,
},
{
k: "idAttribute",
v: 123,
},
...
]
...
}
and index
{
"filterAttributes.k": 1,
"filterAttributes.v": 1,
}
query1
{
$and: [
{ filterAttributes: { $elemMatch: { k: "booleanAttribute", v: true } }},
{ filterAttributes: { $elemMatch: { k: "idAttribute", v: 123 } }}
]
}
query2
{
$and: [
{ filterAttributes: { $elemMatch: { k: "idAttribute", v: 123 } }},
{ filterAttributes: { $elemMatch: { k: "booleanAttribute", v: true } }}
]
}
If the collection has a large amount of true
booleanAttribute
values but low amount of 123
idAttribute
values, then query1 will be magnitudes slower than query2.
It seems that MongoDB can not use an index intersection to use the more selective attribute.
Is there a better way to query the data structure here?