I want to find an account by name (in a MongoDB collection of 50K accounts)

In the usual way: we find with string

db.accounts.find({ name: 'Jon Skeet' })  // indexes help improve performance!

How about with regular expression? Is it an expensive operation?

db.accounts.find( { name: /Jon Skeet/ }) // worry! how indexes work with regex?


According to WiredPrairie:
MongoDB use prefix of RegEx to lookup indexes (ex: /^prefix.*/):

db.accounts.find( { name: /^Jon Skeet/ })  // indexes will help!'

MongoDB $regex

Solution 1

Actually according to the documentation,

If an index exists for the field, then MongoDB matches the regular expression against the values in the index, which can be faster than a collection scan. Further optimization can occur if the regular expression is a prefix expression, which means that all potential matches start with the same string. This allows MongoDB to construct a range from that prefix and only match against those values from the index that fall within that range.


In other words:

For /Jon Skeet/ regex ,mongo will full scan the keys in the index then will fetch the matched documents, which can be faster than collection scan.

For /^Jon Skeet/ regex ,mongo will scan only the range that start with the regex in the index, which will be faster.

Solution 2

In case anyone still has an issue with search performance, there is a way to optimize regex search even if it searches for a word in a sentence (not necessarily at the beginning ^ or the end $ of the string).

The field should have a text index

db.someCollection.createIndex({ someField: "text" })

and the queries on should use regex only after performing a plain search first

db.someCollection.find({ $and: 
    { $text: { $search: "someWord" }}, 
    { someField: { $elemMatch: {$regex: /test/ig, $regex: /other/ig}}}

This ensures that the regex will run only for the results of the initial, plain search, which should be quite fast thanks to the index on this field. It might have a huge impact on search performance, depending on how large the collection is.