Hi, I have this legacy schema
{
"root" : [
{
"key" : [
[
"value",
...
], ...
]
}, ...
]
}
I’m querying the documents with
"root":{
"$elemMatch":{
"key.0":{ // can be 0, 1, 2, etc.
"$elemMatch":{
"$eq":"value" // only value that can change
}
}
}
}
That’s currently working but it’s really slow, I’ve tried adding a multi key index {"root.key.0": 1}
, totalIndexSize() reports about 59kb more that without it, that means is does have something but when I run the exact same query I no longer get any results.
Explain outputs the following:
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 0,
"works" : 1,
"advanced" : 0,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"root.key.0" : 1
},
"indexName" : "indexTest",
"isMultiKey" : true,
"multiKeyPaths" : {
"root.key.0" : [
"root",
"root.key"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"root.key.0" : [
"[\"value\", \"value\"]"
]
},
"keysExamined" : 0,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
It’s clearly trying to match and the “indexBounds” is ok but it says “keysExamined”: 0.
I’ve tried several index combinations like “root.key”, “root.0.key.0”, “root.0.key.0.0” but those have indexBounds: [MinKey, MaxKey] so they’re not useful.
If I use $regex instead of $eq I get “keysExamined”: 1 but still no results.
I’ve also tried to see if there was any way of seeing what was really in the index but I don’t know how to do that.
We do have a new schema but this one can’t be migrated without manually checking each document, so it’s not really a choice.
Is there any way to check the index or indexing so that this query works faster?