Index Bounds on Mongo Regex Search

Question

I'm using MongoDB, and I have a collection of documents with the following structure:

{
    fName:"Foo",
    lName:"Barius",
    email:"[email protected]",
    search:"foo barius"
}

I am building a function that will perform a regular expression search on the search field. To optimize performance, I have indexed this collection on the search field. However, things are still a bit slow. So I ran an explain() on a sample query:

db.Collection.find({search:/bar/}).explain();

Looking under the winning plan, I see the following index bounds used:

"search": [
        "[\"\", {})",
        "[/.*bar.*/, /.*bar.*/]"
]

The second set makes sense - it's looking from anything that contains bar to anything that contains bar. However, the first set baffles me. It appears to be looking in the bounds of "" inclusive to {} exclusive. I'm concerned that this extra set of bounds is slowing down my query. Is it necessary to keep? If it's not, how can I prevent it from being included?

Answer 1

I think it's just the way mongodb works with regex (see https://scalegrid.io/blog/mongodb-regular-expressions-indexes-performance/). Just watch out for nscanned/totalKeysExamined value, if it's too large then the index is useless for your query.

See also: MongoDB, performance of query by regular expression on indexed fields

Answer 2

This is the way mongo works with this type of regex and an index. What I mean is that you are searching for /bar/ instead of /^bar/.

When you specify an index on that field, it is indexing from the first character. So "Foo barius" is indexed beginning with F. Since you are searching for "bar" anywhere in the field you have to search the entire index on that field looking *bar*.

The first line in your explain says look at every record in the index.

The second line say, give me only those indices from (1) that have bar in them.

Bottom line: Design your records so they use the index efficiently. In the case of strings, make sure your searches are at the beginning of the string, e.g., /^bar/. If I'm going to search by last name then it needs to occur first in an indexed field.

As an exercise do an explain on /^bar/ instead. You won't get your data, but the first index bounds will be something like /^bar/ to /^bas/.

I hope my stream of consciousness answer is helpful.

UDude