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.

Improve this question

1 Answer 1

Reset to default
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 })
Improve this answer
2
  • 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, 2019 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, 2019 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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