why this MongoDB query took too long?

Question

I've been running a query on MongoDB database but it took too long and I can't get what am I missing.

this is executionStats for this query. I'm using MongoDB 3.4 on my operational server and use Mongoose to run query on it. mycollection has about 400 millions of documents.

can anyone find something out about this? please inform me if some more details is required.

{
    "op" : "query",
    "ns" : "mydb.mycollection",
    "query" : {
        "find" : "mycollection",
        "filter" : {
            "createdAt" : {
                "$gte" : ISODate("2022-08-05T19:30:00Z"),
                "$lte" : ISODate("2022-09-07T11:17:00Z")
            },
            "clientName" : {
                "$in" : [
                    "ap2015"
                ]
            },
            "scopeName" : {
                "$in" : [
                    "global"
                ]
            },
            "status" : "FAILED"
        },
        "sort" : {
            "createdAt" : -1
        },
        "projection" : {
            
        },
        "limit" : 20,
        "returnKey" : false,
        "showRecordId" : false
    },
    "keysExamined" : 19388,
    "docsExamined" : 20,
    "fromMultiPlanner" : true,
    "cursorExhausted" : true,
    "numYield" : 8795,
    "locks" : {
        "Global" : {
            "acquireCount" : {
                "r" : NumberLong(17592)
            }
        },
        "Database" : {
            "acquireCount" : {
                "r" : NumberLong(8796)
            }
        },
        "Collection" : {
            "acquireCount" : {
                "r" : NumberLong(8796)
            }
        }
    },
    "nreturned" : 20,
    "responseLength" : 23901,
    "protocol" : "op_query",
    "millis" : 200328,
    "planSummary" : "IXSCAN { clientName: 1.0, createdAt: -1.0, status: 1.0, scopeName: 1.0, Status: 1.0 }",
    "execStats" : {
        "stage" : "LIMIT",
        "nReturned" : 20,
        "executionTimeMillisEstimate" : 984,
        "works" : 19389,
        "advanced" : 20,
        "needTime" : 19368,
        "needYield" : 0,
        "saveState" : 8795,
        "restoreState" : 8795,
        "isEOF" : 1,
        "invalidates" : 0,
        "limitAmount" : 20,
        "inputStage" : {
            "stage" : "FETCH",
            "nReturned" : 20,
            "executionTimeMillisEstimate" : 984,
            "works" : 19388,
            "advanced" : 20,
            "needTime" : 19368,
            "needYield" : 0,
            "saveState" : 8795,
            "restoreState" : 8795,
            "isEOF" : 0,
            "invalidates" : 0,
            "docsExamined" : 20,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 20,
                "executionTimeMillisEstimate" : 974,
                "works" : 19388,
                "advanced" : 20,
                "needTime" : 19368,
                "needYield" : 0,
                "saveState" : 8795,
                "restoreState" : 8795,
                "isEOF" : 0,
                "invalidates" : 0,
                "keyPattern" : {
                    "clientName" : 1,
                    "createdAt" : -1,
                    "status" : 1,
                    "scopeName" : 1,
                    "Status" : 1
                },
                "indexName" : "clientName_1_createdAt_-1_status_1_scopeName_1_Status_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "clientName" : [ ],
                    "createdAt" : [ ],
                    "status" : [ ],
                    "scopeName" : [ ],
                    "Status" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : true,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "clientName" : [
                        "[\"ap2015\", \"ap2015\"]"
                    ],
                    "createdAt" : [
                        "[new Date(1662549420000), new Date(1659727800000)]"
                    ],
                    "status" : [
                        "[\"FAILED\", \"FAILED\"]"
                    ],
                    "scopeName" : [
                        "[\"global\", \"global\"]"
                    ],
                    "Status" : [
                        "[MinKey, MaxKey]"
                    ]
                },
                "keysExamined" : 19388,
                "seeks" : 19369,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    },
    "ts" : ISODate("2022-09-06T11:28:59.115Z"),
    "client" : "192.168.105.130",
    "allUsers" : [
        {
            "user" : "backofficeap2015",
            "db" : "mydb"
        }
    ],
    "user" : "backofficeap2015@mydb"
}

Charchit Kapoor · Answer

For composite indexes, MongoDB recommends using the rule equality, sort, range, in which keys on which equality comparison happens come first, then the keys used for sorting, and then the keys used for range comparisons. The index criteria I suggested, follow just that.

{ clientName: 1.0, status: 1.0, scopeName: 1.0, Status: 1.0, createdAt: -1.0 }

Since you were sorting by createdAt and also applying a range filter based on that, I moved that key at the end, so that all the equality matches happens, and the number of documents for sorting and range filter, get reduced as well.

You can read more about the rule here.

why this MongoDB query took too long?

1 Answers