Why is Mongo not recognising the non-dotted version of an index?

Question

I have a collection in Mongo like this:

{
  'key': {
    'a': <value>,
    'b': <value>
  },
  'data': <stuff>
}

It has an index on key:

db.collection.createIndex( { 'key.a': 1, 'key.b': 1 } )

Now I would expect these two queries to both use the index:

db.collection.find( { 'key.a': 13, 'key.b': 37 } )
db.collection.find( { 'key': {'a': 13, 'b': 37 } } )

However, as an .explain() statement shows, Mongo will only use the index (IXSCAN) on the first query and not on the second (it uses a COLLSCAN).

Now I'm curious, why doesn't Mongo use the index on the non-dotted version of the query?

score 0 · Answer 1 · answered May 15 '20 at 11:31

Not sure why it isn't using the indexes correctly (What version of MongoDB are you using?), however if you hint the indexes it uses them.

> db.test.insert({"top" : { "key": { "a": 1, "b": 2}, "data" : "blahblah"}})
WriteResult({ "nInserted" : 1 })
> db.test.createIndex({ "key.a":1, "key.b" : 1 })
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.test.find( {"key" :{"a": 1, "b": 2}}).hint({"key.a":1, "key.b":1}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.test",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "key" : {
                                "$eq" : {
                                        "a" : 1,
                                        "b" : 2
                                }
                        }
                },
                "queryHash" : "281CFA9E",
                "planCacheKey" : "281CFA9E",
                "winningPlan" : {
                        "stage" : "FETCH",
                        "filter" : {
                                "key" : {
                                        "$eq" : {
                                                "a" : 1,
                                                "b" : 2
                                        }
                                }
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "key.a" : 1,
                                        "key.b" : 1
                                },
                                "indexName" : "key.a_1_key.b_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "key.a" : [ ],
                                        "key.b" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "key.a" : [
                                                "[MinKey, MaxKey]"
                                        ],
                                        "key.b" : [
                                                "[MinKey, MaxKey]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "LAPTOP-O235KC41",
                "port" : 27017,
                "version" : "4.2.0",
                "gitVersion" : "a4b751dcf51dd249c5865812b390cfd1c0129c30"
        },
        "ok" : 1
}
>

Why is Mongo not recognising the non-dotted version of an index?

1 Answers1