Not able to create covered query in MongoDB

Question

I am facing problem to create covered query. I am using Mongo 3 latest version. Here is my sample data which I have inserted 10006 documents into MongoDB.

db.order.insert({ _id: 1, cust_id: "abc1", ord_date: ISODate("2012-11-02T17:04:11.102Z"), status: "A", amount: 50 })
db.order.insert({ _id: 2, cust_id: "xyz1", ord_date: ISODate("2013-10-01T17:04:11.102Z"), status: "A", amount: 100 })
db.order.insert({ _id: 3, cust_id: "xyz1", ord_date: ISODate("2013-10-12T17:04:11.102Z"), status: "D", amount: 25 })
db.order.insert({ _id: 4, cust_id: "xyz1", ord_date: ISODate("2013-10-11T17:04:11.102Z"), status: "D", amount: 125 })
db.order.insert({ _id: 5, cust_id: "abc1", ord_date: ISODate("2013-11-12T17:04:11.102Z"), status: "A", amount: 25 })

For Covered Query, All the fields in the query are part of an index so I have created index for status, ord_date, cust_id and amount fields like :

db.orders.createIndex({status: 1})
db.orders.createIndex({amount: 1})
db.orders.createIndex({ord_date: 1})
db.orders.createIndex({cust_id: 1})

I have executed following query.

          db.orders.find(
                 {status : "A"},{ord_date : 1, cust_id : 1}
          ).sort({ amount: -1 }).explain()

But This explain query returns executionStats.totalDocsExamined = 200 instead of executionStats.totalDocsExamined = 0. means it is scan documents when I execute query. In Mongo 3, We can check index covered a query using executionStats.totalDocsExamined instead of indexOnly.

Can anyone please suggest me what I am doing wrong in covered query ?

Here is my output after index suggestion by Markus:

{
"queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "local.orders",
    "indexFilterSet" : false,
    "parsedQuery" : {
        "status" : {
            "$eq" : "A"
        }
    },
    "winningPlan" : {
        "stage" : "PROJECTION",
        "transformBy" : {
            "_id" : 1,
            "ord_date" : 1,
            "cust_id" : 1
        },
        "inputStage" : {
            "stage" : "SORT",
            "sortPattern" : {
                "amount" : -1
            },
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "status" : {
                        "$eq" : "A"
                    }
                },
                "direction" : "forward"
            }
        }
    },
    "rejectedPlans" : [ ]
},
"executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 10004,
    "executionTimeMillis" : 70,
    "totalKeysExamined" : 0,
    "totalDocsExamined" : 10018,
    "executionStages" : {
        "stage" : "PROJECTION",
        "nReturned" : 10004,
        "executionTimeMillisEstimate" : 70,
        "works" : 20026,
        "advanced" : 10004,
        "needTime" : 10021,
        "needFetch" : 0,
        "saveState" : 157,
        "restoreState" : 157,
        "isEOF" : 1,
        "invalidates" : 0,
        "transformBy" : {
            "_id" : 1,
            "ord_date" : 1,
            "cust_id" : 1
        },
        "inputStage" : {
            "stage" : "SORT",
            "nReturned" : 10004,
            "executionTimeMillisEstimate" : 70,
            "works" : 20026,
            "advanced" : 10004,
            "needTime" : 10020,
            "needFetch" : 0,
            "saveState" : 157,
            "restoreState" : 157,
            "isEOF" : 1,
            "invalidates" : 0,
            "sortPattern" : {
                "amount" : -1
            },
            "memUsage" : 960384,
            "memLimit" : 33554432,
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "status" : {
                        "$eq" : "A"
                    }
                },
                "nReturned" : 10004,
                "executionTimeMillisEstimate" : 10,
                "works" : 10020,
                "advanced" : 10004,
                "needTime" : 15,
                "needFetch" : 0,
                "saveState" : 157,
                "restoreState" : 157,
                "isEOF" : 1,
                "invalidates" : 0,
                "direction" : "forward",
                "docsExamined" : 10018
            }
        }
    },
    "allPlansExecution" : [ ]
},
"serverInfo" : {
    "host" : "pcd32",
    "port" : 27017,
    "version" : "3.0.7",
    "gitVersion" : "6ce7cbe8c6b899552dadd907604559806aa2esd5"
}

}

please show complete output of `.explain()` – styvane Oct 23 '15 at 08:33 — styvane, Oct 23 '15 at 08:33
I have updated my output in my question. – sus007 Oct 23 '15 at 08:59 — sus007, Oct 23 '15 at 08:59

score 1 · Answer 1 · answered Oct 23 '15 at 09:37

1

While there are index intersections in MongoDB, they can be quite tricky to utilize. However, sticking to a rule of thumb is a rather safe bet:

When creating queries MongoDB, assume that only one index can be used at a time

This is especially true for covered queries, as detailed in the docs:

An index covers a query when both of the following apply:

all the fields in the query are part of an index, and

all the fields returned in the results are in the same index.

Having a compound index doesn't have drawbacks, when carefully crafted, as queries using only parts of that index can use it, too.

So in order to make your query covered, you need to have all keys you want to return in your index. Since you did not limit the fields returned ("projection" in MongoDB terms), I assume you need the _id field to be returned as well. Furthermore, your index should reflect your sorting order. So your index should look like:

db.orders.createIndex({_id:1,status:1, ord_date:1,cust_id:1,amount:-1})

for your query. Order matters, so in order to make best use of the newly created index, other queries should adhere to the same order of fields.

answered Oct 23 '15 at 09:37

Markus W Mahlberg

19,711
6
65
89

I have created only "db.orders.createIndex({_id:1,status:1, ord_date:1,cust_id:1,amount:-1})" index according my return fields. But still result seems executionStats.totalDocsExamined = 200 instead of 0. May I need to create any other indexes except your index ? – sus007 Oct 23 '15 at 12:36
@sus007 Please add the output of "yourQuery.explain()" after you created the index to your question. – Markus W Mahlberg Oct 23 '15 at 17:45
I have created index like that : db.orders.createIndex({status: 1, amount: 1, ord_date: 1, cust_id: 1}); and I solved this problem. So order of field in index is very important for covered query. – sus007 Oct 26 '15 at 10:50
Markus, I have tried with your suggested index but it will not return 0 value for totalDocsExamine in explain method. Please correct me if I am wrong. – sus007 Oct 26 '15 at 10:56
Please edit your post and add the output of your queries ".explain()" – Markus W Mahlberg Oct 26 '15 at 11:24
I have create another question regarding covered query. so please check it. Thanks. http://stackoverflow.com/questions/33345030/how-to-create-covered-query-index-in-mongodb – sus007 Oct 26 '15 at 11:48
I have updated my post with new .explain() output after creating index as you suggest me. – sus007 Oct 26 '15 at 11:58

anish · Answer 2 · 2015-10-23T20:34:40.577

1

If you also need the _id field, then the below compound index should give you a covered query:

 db.order.createIndex({status:1, amount:-1, ord_date:1, cust_id :1, _id:1})

If you don't need the _id field then use _id : 0 in the find(), so that _id is not retrieved and you can remove it from the index as well. Note that in a covered query, ordering of the fields as compared to the actual query being executed is important for the index to be used in the execution of the query.

edited Oct 23 '15 at 20:34

answered Oct 23 '15 at 20:18

anish

482
2
8

I have create another question regarding covered query. so please check it. Thanks. http://stackoverflow.com/questions/33345030/how-to-create-covered-query-index-in-mongodb – sus007 Oct 26 '15 at 11:49

Not able to create covered query in MongoDB

2 Answers2