Slow regexp query in MongoDB

Question

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?

Nat · Accepted Answer · 2012-04-10 03:38:10Z

4

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.

answered Apr 10, 2012 at 3:38

Nat

3,72723 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Slow regexp query in MongoDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related