1
I have a problem using the .find() mongoDB with Regexp. I have the following command:
db.collection.find({"city": /SAOPAULO/})
When I do:
db.collection.find({"city": /SAO/})
it searches on time, but when I pass a big word it takes to load:
db.collection.find({"city": /SAOPAULO/})
Does anyone know what it can be?
SAOPAULOis slow compared toSAO.
1
It seems that is an expected behavior.
According to the mongodb documentation (griffin 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 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.
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.
That is, to /SAOPAULO/, Mongo will search for almost all the index keys to find the records that match this search, in which it will probably be faster than a search for the entire collection of records (which already makes up for the index creation).
To \SAO\, Mongo will scan only the range of indices that start with this value. Thus, it will finish faster the query in the index, because it is a smaller value than \SAOPAULO\ (in which it will traverse more parts of the index).
Thus, it is expected that the query for the initial portion of the desired word will be faster than for the entire word.
I tried using /^a/, /^a.*/, /^a.*$/, /a/. The fastest was /a/ but still is slow the others are taking on average 3 seconds to respond. Using /sao/ the search takes 0ms and when I put /saopaulo/ time goes to 1s
I’m sorry, but I don’t quite understand your question. I’m finding it strange that the response time varies a lot in relation to the size of my query
@user, edited, see if my explanation became clearer.
Now I get it. VLW!
I’ve been reading the documentation of Mongo and found the $text $serach. will it help me?
@user, I believe not. What this method does is simplify the search, taking a little bit of code from the user and assuming some things, working more like a utility. It doesn’t seem to make a difference in performance. However, you can try and see if you feel any difference :)
Using $text $search I got the performance I wanted. but I had another problem that it does not work with regex
I got it. It’s true, because you don’t use regex, there could be variation in performance with text (forgot about Regex).
Browser other questions tagged sql mongodb
You are not signed in. Login or sign up in order to post.
The query works, but gets slower with
SAOPAULOcompared to theSAO? This behavior is consistent?– Dherik
@Dherik That’s right. Increasing the slower string Mongo gets. I set a limit of 20 responses per call with . find(20) to get a little faster.
– usuario
Using index for this column?
– Dherik
@Dherik Yes. I am using index in the columns I use. Does it have anything related to the amount of data in my table?
– usuario
Yes. From what I read here, it seems that when searching for a prefix, the search ends up being faster. See this link. It seems to be a case similar to yours.
– Dherik