0

I have added created a collection first

and created index db.first.createIndex({a:1, b:1, c:1, d:1, e:1, f:1});

and inserted data

db.first.insert({a:1, b:2, c:3, d:4, e:5, f:6});
db.first.insert({a:1, b:6});

When making queries like

db.first.find({f: 6, a:1, c:3}).sort({b: -1}).explain();

index are used

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "myproject.first",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "a" : {
                        "$eq" : 1
                    }
                },
                {
                    "c" : {
                        "$eq" : 3
                    }
                },
                {
                    "f" : {
                        "$eq" : 6
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "a" : 1,
                    "b" : 1,
                    "c" : 1,
                    "d" : 1,
                    "e" : 1,
                    "f" : 1
                },
                "indexName" : "a_1_b_1_c_1_d_1_e_1_f_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "a" : [ ],
                    "b" : [ ],
                    "c" : [ ],
                    "d" : [ ],
                    "e" : [ ],
                    "f" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "backward",
                "indexBounds" : {
                    "a" : [
                        "[1.0, 1.0]"
                    ],
                    "b" : [
                        "[MaxKey, MinKey]"
                    ],
                    "c" : [
                        "[3.0, 3.0]"
                    ],
                    "d" : [
                        "[MaxKey, MinKey]"
                    ],
                    "e" : [
                        "[MaxKey, MinKey]"
                    ],
                    "f" : [
                        "[6.0, 6.0]"
                    ]
                }
            }
        },
        "rejectedPlans" : [
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "b" : -1
                },
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "$and" : [
                                {
                                    "c" : {
                                        "$eq" : 3
                                    }
                                },
                                {
                                    "f" : {
                                        "$eq" : 6
                                    }
                                }
                            ]
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "a" : 1
                            },
                            "indexName" : "a_1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "a" : [ ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "a" : [
                                    "[1.0, 1.0]"
                                ]
                            }
                        }
                    }
                }
            }
        ]
    },
    "serverInfo" : {
        "host" : "Manishs-MacBook-Pro.local",
        "port" : 27017,
        "version" : "3.6.4",
        "gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
    },
    "ok" : 1
}

but when I use or query

db.first.find({ $or: [{f: 6}, {a:1}]}).explain();

index is not used instead columns are scanned

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "myproject.first",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$or" : [
                {
                    "a" : {
                        "$eq" : 1
                    }
                },
                {
                    "f" : {
                        "$eq" : 6
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "SUBPLAN",
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "$or" : [
                        {
                            "a" : {
                                "$eq" : 1
                            }
                        },
                        {
                            "f" : {
                                "$eq" : 6
                            }
                        }
                    ]
                },
                "direction" : "forward"
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "Manishs-MacBook-Pro.local",
        "port" : 27017,
        "version" : "3.6.4",
        "gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
    },
    "ok" : 1
}

Please let me know if I am doing something wrong.

0

The issue here is, you've created a compound index on {a:1, b:1, c:1, d:1, e:1, f:1} fields but you're not following the order of the index. So your queries should contain all the fields in the same order that you've constructed your index. Since the field 'f' is in the tail end of the index, your queries will not utilize or even identify it

Your queries:

db.first.find({f: 6, a:1, c:3}).sort({b: -1})
db.first.find({ $or: [{f: 6}, {a:1}]})

To make both your above queries use the index, you should build the compound index as below:

db.first.createIndex({ f:1, a:1, c:1 })
  • Thanx for quick response. The query db.first.find({f: 6, a:1, c:3}).sort({b: -1}) does use the index as shown in the response object but the query with or does not. – FastTurtle Aug 23 at 7:47
  • Because your 2nd query is using $or operator, which must either have individual indexes on the fields or you build compound index in the right order – Anban Aug 23 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.