I'm having a problem using .find() of mongoDB with RegExp. I have the following command:
db.collection.find({"city": /SAOPAULO/})
When I do:
db.collection.find({"city": /SAO/})
It searches in time, but when I pass a large word it takes a while to load:
db.collection.find({"city": /SAOPAULO/})
Does anyone know what it can be?
SAOPAULOgets slow compared toSAO.
It seems like expected behavior.
According to MongoDB documentation (emphasis mine):
For case sensitive regular expression queries, 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 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 fails within that range.
A regular expression is a prefix expression if it starts with a caret (^) or a left anchor (\ A), followed by a string of simple symbols. For example, the regex /^abc.*/ will be optimized by matching only against the values from the index that start with abc.
In other words, for /SAOPAULO/ , the mongo will search almost every index key to find the records that match this search, which will probably be faster than a search for the whole collection of records (the which already compensates for the creation of the index).
For \SAO\ , mongo will only scan the range of indexes that start with this value. Thus, it will end the index query faster because it is a value less than \SAOPAULO\ (in which you will scroll through more parts of the index).
Thus, the query for the initial portion of the desired word is expected to be faster than for the whole word.