Home:ALL Converter>MongoDB use index with $nin seems not to work in combination with $regex

MongoDB use index with $nin seems not to work in combination with $regex

Ask Time:2017-01-29T01:35:45         Author:TJR

Json Formatter

It seems that my index in my MongoDB is not correct.

I have created 3 indexes. These:

{
  _id: 1
}
{
  isbn: 1
}
{
  _id: 1,
  isbn: 1
}

When doing a query with isbn or _id its working perfect. Even with isbn and _id. For example:

db.getCollection('books').find({
  isbn: {
    $regex: '^978048627.*'
  }, 
  _id: 'vGXejKQH5kw8Kfutk'
}

needs around 3ms.

But lets now say I want to search for an ISBN and need to exclude some _ids - I do this:

db.getCollection('books').find({
  isbn: {
    $regex: '^97804862731.*'
  }, 
  _id: {
    $nin:['vGXejKQH5kw8Kfutk']
  }
})

Now its not working as it should. The query took more then 10 seconds!

When I do a isbn search without $regex but with $nin its works perfect - again around 3ms for the query. Example:

db.getCollection('books').find({
  isbn: '9780486273136', 
  _id: {
    $nin:['vGXejKQH5kw8Kfutk']
  }
})

Am I doing something wrong ? And why the index is not working correctly as it should ?

Here is the .explain() output when querying the 10 seconds query:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "***.books",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "isbn" : /^97804862731.*/
                }, 
                {
                    "$not" : {
                        "_id" : {
                            "$in" : [ 
                                "vGXejKQH5kw8Kfutk"
                            ]
                        }
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "filter" : {
                    "isbn" : /^97804862731.*/
                },
                "keyPattern" : {
                    "isbn" : 1.0,
                    "_id" : 1.0
                },
                "indexName" : "isbn_1__id_1",
                "isMultiKey" : false,
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 1,
                "direction" : "forward",
                "indexBounds" : {
                    "isbn" : [ 
                        "[\"97804862731\", \"97804862732\")", 
                        "[/^97804862731.*/, /^97804862731.*/]"
                    ],
                    "_id" : [ 
                        "[MinKey, \"vGXejKQH5kw8Kfutk\")", 
                        "(\"vGXejKQH5kw8Kfutk\", MaxKey]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "FETCH",
                "filter" : {
                    "isbn" : /^97804862731.*/
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "_id" : 1
                    },
                    "indexName" : "_id_",
                    "isMultiKey" : false,
                    "isUnique" : true,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 1,
                    "direction" : "forward",
                    "indexBounds" : {
                        "_id" : [ 
                            "[MinKey, \"vGXejKQH5kw8Kfutk\")", 
                            "(\"vGXejKQH5kw8Kfutk\", MaxKey]"
                        ]
                    }
                }
            }, 
            {
                "stage" : "FETCH",
                "filter" : {
                    "$not" : {
                        "_id" : {
                            "$in" : [ 
                                "vGXejKQH5kw8Kfutk"
                            ]
                        }
                    }
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "filter" : {
                        "isbn" : /^97804862731.*/
                    },
                    "keyPattern" : {
                        "isbn" : 1
                    },
                    "indexName" : "isbn_1",
                    "isMultiKey" : false,
                    "isUnique" : true,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 1,
                    "direction" : "forward",
                    "indexBounds" : {
                        "isbn" : [ 
                            "[\"97804862731\", \"97804862732\")", 
                            "[/^97804862731.*/, /^97804862731.*/]"
                        ]
                    }
                }
            }
        ]
    },
    "serverInfo" : {
        "host" : "Ubuntu-1604-xenial-64-minimal",
        "port" : 27017,
        "version" : "3.2.11",
        "gitVersion" : "009580ad490190ba33d1c6253ebd8d91808923e4"
    },
    "ok" : 1.0
}

Author:TJR,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/41912754/mongodb-use-index-with-nin-seems-not-to-work-in-combination-with-regex
yy