Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Slow regexp query in MongoDB

Tags:

regex

mongodb

I have 1.5 million of documents in a collection with an indexed "name" field. Query like db.things.find({name: /^foo/i}) takes about of 5 secs which is very slow. Similar MySQL table with the same records performs SELECT * FROM things WHERE name LIKE 'foo%' in less then 10 ms.

The mongo's explain:

db.things.find({name: /^foo/i}).limit(10).explain()
{
    "cursor" : "BtreeCursor name_1 multi",
    "nscanned" : 325730,
    "nscannedObjects" : 10,
    "n" : 10,
    "millis" : 4758,
    "nYields" : 89,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
    "indexBounds" : {
        "name" : [
            [
                "",
                {

                }
            ],
            [
                /^foo/i,
                /^foo/i
            ]
        ]
    }
}

So is regexp query that slow in mongo or am I doing it wrong?

like image 896
tycooon Avatar asked Oct 09 '22 04:10

tycooon


1 Answers

case insensitive regex search will be slow as it cannot take advantage of the index effectively. If you only use the field for searching, you should consider storing text in all lowercase form and search with case-sensitive regex.

like image 179
Nat Avatar answered Oct 12 '22 11:10

Nat