CODEWITHSUNDEEP
mongodb
Mars Zhu
Mars Zhu

Reputation: 296

How to improve mongo queries with multikey index in array

I just found out that 1 of our queries is quite slow. We use a multikey index in an array and not sure how we can actually improve this query.

Our data structure:

account: [{
    accountId:string,
    service:string
}]

Index key is:

{"account.accountId":1, "account.service":1}

Here's my query: 

db.model.find({"account.accountId":"12345", "account.service":"GameCenter"}).explain();

executionStats (1 million records)

"executionStats" : {
    "nReturned" : 1,
    "executionTimeMillis" : 325,
    "totalKeysExamined" : 122744,
    "totalDocsExamined" : 122743,
    "executionStages" : {
        "stage" : "SHARD_MERGE",
        "nReturned" : 1,
        "executionTimeMillis" : 325,
        "totalKeysExamined" : 122744,
        "totalDocsExamined" : 122743,
        "totalChildMillis" : NumberLong(834),
        "shards" : [
            {
                "shardName" : "rs1",
                "executionSuccess" : true,
                "executionStages" : {
                    "stage" : "SHARDING_FILTER",
                    "nReturned" : 0,
                    "executionTimeMillisEstimate" : 300,
                    "works" : 40999,
                    "advanced" : 0,
                    "needTime" : 40998,
                    "needYield" : 0,
                    "saveState" : 320,
                    "restoreState" : 320,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "chunkSkips" : 0,
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "account.accountId" : {
                                "$eq" : "G:8183971619"
                            }
                        },
                        "nReturned" : 0,
                        "executionTimeMillisEstimate" : 300,
                        "works" : 40999,
                        "advanced" : 0,
                        "needTime" : 40998,
                        "needYield" : 0,
                        "saveState" : 320,
                        "restoreState" : 320,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "docsExamined" : 40998,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "nReturned" : 40998,
                            "executionTimeMillisEstimate" : 30,
                            "works" : 40999,
                            "advanced" : 40998,
                            "needTime" : 0,
                            "needYield" : 0,
                            "saveState" : 320,
                            "restoreState" : 320,
                            "isEOF" : 1,
                            "invalidates" : 0,
                            "keyPattern" : {
                                "account.service" : 1,
                                "account.accountId" : 1
                            },
                            "indexName" : "account.service_1_account.accountId_1",
                            "isMultiKey" : true,
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,
                            "direction" : "forward",
                            "indexBounds" : {
                                "account.service" : [
                                    "[\"GameCenter\", \"GameCenter\"]"
                                ],
                                "account.accountId" : [
                                    "[MinKey, MaxKey]"
                                ]
                            },
                            "keysExamined" : 40998,
                            "dupsTested" : 40998,
                            "dupsDropped" : 0,
                            "seenInvalidated" : 0
                        }
                    }
                }
            },
            {
                "shardName" : "rs2",
                "executionSuccess" : true,
                "executionStages" : {
                    "stage" : "SHARDING_FILTER",
                    "nReturned" : 1,
                    "executionTimeMillisEstimate" : 240,
                    "works" : 40612,
                    "advanced" : 1,
                    "needTime" : 40610,
                    "needYield" : 0,
                    "saveState" : 317,
                    "restoreState" : 317,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "chunkSkips" : 0,
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "account.accountId" : {
                                "$eq" : "G:8183971619"
                            }
                        },
                        "nReturned" : 1,
                        "executionTimeMillisEstimate" : 230,
                        "works" : 40612,
                        "advanced" : 1,
                        "needTime" : 40610,
                        "needYield" : 0,
                        "saveState" : 317,
                        "restoreState" : 317,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "docsExamined" : 40610,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "nReturned" : 40611,
                            "executionTimeMillisEstimate" : 70,
                            "works" : 40612,
                            "advanced" : 40611,
                            "needTime" : 0,
                            "needYield" : 0,
                            "saveState" : 317,
                            "restoreState" : 317,
                            "isEOF" : 1,
                            "invalidates" : 0,
                            "keyPattern" : {
                                "account.service" : 1,
                                "account.accountId" : 1
                            },
                            "indexName" : "account.service_1_account.accountId_1",
                            "isMultiKey" : true,
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,
                            "direction" : "forward",
                            "indexBounds" : {
                                "account.service" : [
                                    "[\"GameCenter\", \"GameCenter\"]"
                                ],
                                "account.accountId" : [
                                    "[MinKey, MaxKey]"
                                ]
                            },
                            "keysExamined" : 40611,
                            "dupsTested" : 40611,
                            "dupsDropped" : 0,
                            "seenInvalidated" : 0
                        }
                    }
                }
            },
            {
                "shardName" : "rs3",
                "executionSuccess" : true,
                "executionStages" : {
                    "stage" : "SHARDING_FILTER",
                    "nReturned" : 0,
                    "executionTimeMillisEstimate" : 270,
                    "works" : 41136,
                    "advanced" : 0,
                    "needTime" : 41135,
                    "needYield" : 0,
                    "saveState" : 321,
                    "restoreState" : 321,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "chunkSkips" : 0,
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "account.accountId" : {
                                "$eq" : "G:8183971619"
                            }
                        },
                        "nReturned" : 0,
                        "executionTimeMillisEstimate" : 270,
                        "works" : 41136,
                        "advanced" : 0,
                        "needTime" : 41135,
                        "needYield" : 0,
                        "saveState" : 321,
                        "restoreState" : 321,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "docsExamined" : 41135,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "nReturned" : 41135,
                            "executionTimeMillisEstimate" : 90,
                            "works" : 41136,
                            "advanced" : 41135,
                            "needTime" : 0,
                            "needYield" : 0,
                            "saveState" : 321,
                            "restoreState" : 321,
                            "isEOF" : 1,
                            "invalidates" : 0,
                            "keyPattern" : {
                                "account.service" : 1,
                                "account.accountId" : 1
                            },
                            "indexName" : "account.service_1_account.accountId_1",
                            "isMultiKey" : true,
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,
                            "direction" : "forward",
                            "indexBounds" : {
                                "account.service" : [
                                    "[\"GameCenter\", \"GameCenter\"]"
                                ],
                                "account.accountId" : [
                                    "[MinKey, MaxKey]"
                                ]
                            },
                            "keysExamined" : 41135,
                            "dupsTested" : 41135,
                            "dupsDropped" : 0,
                            "seenInvalidated" : 0
                        }
                    }
                }
            }
        ]
    }
},

To summarise, the query do use the indexes but scan a lot of documents to get the results (122k / 1million). We couldn't use covered index as we need to return some other information too.

Also something werid: I'm querying for a unique accoundId, but my indexBounds for account.accountId is:

"account.accountId" : [
    "[MinKey, MaxKey]"
]

Thank you for your help!

Mars

Upvotes: 3

Views: 636

Answers (1)

Mars Zhu
Mars Zhu

Reputation: 296

After reading the executionStatus carefully, the query is actually scanning all indexes since "accountId" indexBounds is [MinKey, MaxKey].

I'm able to resolve this issue with $elemMatch

db.model.find({
    account:{
        $elemMatch:{
            accountId:"111", 
            service:"facebook"
        }
    }
})

Upvotes: 2

Related Questions