Mongodb query execution time

Question

I used Query below its take to much time

Query

db.saleOrder.find({"currentStatus._id":"147"},{"_id":1}).limit(10).explain("executionStats")

ExecutionStats result

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "db_erp_tube.saleOrder",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "currentStatus._id" : {
                "$eq" : "147"
            }
        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 10,
            "inputStage" : {
                "stage" : "PROJECTION",
                "transformBy" : {
                    "_id" : 1
                },
                "inputStage" : {
                    "stage" : "COLLSCAN",
                    "filter" : {
                        "currentStatus._id" : {
                            "$eq" : "147"
                        }
                    },
                    "direction" : "forward"
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 10,
        "executionTimeMillis" : 8673,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 3458482,
        "executionStages" : {
            "stage" : "LIMIT",
            "nReturned" : 10,
            "executionTimeMillisEstimate" : 8460,
            "works" : 3458484,
            "advanced" : 10,
            "needTime" : 3458473,
            "needYield" : 0,
            "saveState" : 27019,
            "restoreState" : 27019,
            "isEOF" : 1,
            "invalidates" : 0,
            "limitAmount" : 10,
            "inputStage" : {
                "stage" : "PROJECTION",
                "nReturned" : 10,
                "executionTimeMillisEstimate" : 8450,
                "works" : 3458483,
                "advanced" : 10,
                "needTime" : 3458473,
                "needYield" : 0,
                "saveState" : 27019,
                "restoreState" : 27019,
                "isEOF" : 0,
                "invalidates" : 0,
                "transformBy" : {
                    "_id" : 1
                },
                "inputStage" : {
                    "stage" : "COLLSCAN",
                    "filter" : {
                        "currentStatus._id" : {
                            "$eq" : "147"
                        }
                    },
                    "nReturned" : 10,
                    "executionTimeMillisEstimate" : 8400,
                    "works" : 3458483,
                    "advanced" : 10,
                    "needTime" : 3458473,
                    "needYield" : 0,
                    "saveState" : 27019,
                    "restoreState" : 27019,
                    "isEOF" : 0,
                    "invalidates" : 0,
                    "direction" : "forward",
                    "docsExamined" : 3458482
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "172.16.109",
        "port" : 27017,
        "version" : "4.0.0",
        "gitVersion" : "3b07af3d4f471ae89e8186d33bbb1d5259597d51"
    },
    "ok" : 1,
    "operationTime" : Timestamp(1556365275, 114),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1556365275, 114),
        "signature" : {
            "hash" : BinData(0,"ppu91nKmeiC//+UvdsEbjrBTDLU="),
            "keyId" : NumberLong("6633468944474701825")
        }
    }
}

Answer 1

The query took more than 8 seconds(8673ms) to execute because it had to scan all 3458482 documents in the saleOrder collection before returning 10 documents.

This is stated in the filter stage of the explain output.

"inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "currentStatus._id" : {
                        "$eq" : "147"
                    }
                },

COLLSCAN indicate full collection scan,

 "totalDocsExamined" : 3458482,

This is the number of document scanned before an result is returned.

 "executionTimeMillis" : 8673,

This is the total time taken for the query to run.

As @the_mahasagar suggested, build a index on currentStatus._id can speed up the query a lot, use the command below.

 db.saleOrder.createIndex({ "currentStatus._id": 1})