2

We are currently running our app on MySQL and are planning to move to MongoDB. We have moved some parts already but having issues with MongoRegex performance.

We have an autocomplete search box that joins 6 tables (indexed / non-indexed fields) and returns results super fast on mysql. The same thing on MongoDB performs really slow. It takes about 2.3 seconds only on one collection. The user has to wait for a long time. The connection time is 0.064 secs. Query time 2.36 seconds. I did a bit of Googling and couldn't find a perfect answer. Everyone said MongoRegex is slow. If that's true how are other companies overcoming this problem?

What is best way to improve autocomplete performance / experience when running in on MongoDB?

Mir Adnan
  • 844
  • 11
  • 24
  • As this is for some kind of "autocompletion" feature, your pattern is prefixed by a fixed string. Something like `Mongo.*`/`Mongo%`. So, as a wild guess, _maybe eventually possibly_ MySQL will optimize RE/LIKE search when the left prefix is constant to use an index. _Maybe eventually possibly_ MongoDB is not able to to the same thing ? Do you have a proper index on that field ? – Sylvain Leroux May 09 '15 at 10:21
  • possible duplicate of [Implement auto-complete feature using MongoDB search](http://stackoverflow.com/questions/29892947/implement-auto-complete-feature-using-mongodb-search) – Markus W Mahlberg May 10 '15 at 07:35

2 Answers2

1

First of all, you will have to design your query carefully. Carefully as in, selecting properly indexed fields and designing accordingly. Also if you are using regex make sure you are writing the regex in a way which forces the query to use an indexed field. Something like /^prefix/ will do. [ See this link : http://docs.mongodb.org/manual/reference/operator/query/regex/#index-use ]

I have seen many implementations using range query of mongodb, but Im not sure if thats the best one, since instantaneous results are a key thing.

Apart from that, I have seen some one who recommended prefix-trees. Which effectively stores the prefixes in a field, and then stores all the words starting with that particular prefix in the next fields as an array. This solution sounds convincing and fast, since the prefix fields are supposed to be indexed, but you will have to think about the storage factor also.

Sak90
  • 600
  • 6
  • 19
0

It is hard to tell as we don't have the search query, but worth mentioning that, after taking a look at the documentation, it appears that MongoDB is able to use an index when using RE search if the pattern is prefixed by a constant string only if it is anchored:

http://docs.mongodb.org/manual/reference/operator/query/regex/#index-use

So, to quote the doc:

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.

But /abc.*/ ou /^.*abc/ will not use the index.

Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125