0

I am using mongo java client. I can see x100 slower queries when using regex as compared to normal query for same document. All documents are indexed on field used in query. How do i improve query performance when using regex for searching in mongodb ?

data modeller
  • 11
  • 6
  • You might be able to improve performance a little bit by writing a smarter regex. But in comparison, regex operations will always be slow. Indexes won't help you, regex operations can never use an index. – Tomalak Jan 26 '15 at 10:48
  • 1
    Post some slow queries you are doing and a sample document. @Tomalak there are some cases where indexes are useful with regex (http://docs.mongodb.org/manual/reference/operator/query/regex/#index-use) – joao Jan 26 '15 at 11:02
  • @joao There is *exactly one* case: When your pattern is a prefix pattern. Chances are that the OP's pattern isn't. – Tomalak Jan 26 '15 at 11:08
  • 1
    @Tomalak You should probably delete that first comment. Saying regex operations can never use an index is wrong. – JohnnyHK Jan 26 '15 at 14:36
  • @JohnnyHK They can never use an index. MongoDB tricks you into thinking that by recognizing prefix patterns like `/^prefix(and|a|complex|expression|here)/`and optimizing for them by first selecting only strings that start with `"prefix"` (that operation uses an index) and then running the entire regex on those only (that operation uses no index). This optimization exclusively applies to prefix patterns and speeds up the regex operation in no way. It only reduces input size. If you don't have a prefix pattern or very many strings that start with `"prefix"`, you won't see much of an improvement. – Tomalak Jan 26 '15 at 15:16
  • 1
    @Tomalak Sure, all true. But saying they "can never use an index" is overstating it. – JohnnyHK Jan 26 '15 at 15:23
  • @JohnnyHK I disagree. It's saying things as they are. The index is used by a "starts-with" comparison that MongoDB helpfully inserts for you. It's important to know the technical difference at this point. I also doubt that MongoDB is capable of optimizing for regular expressions that contain multiple start-of-string anchors (`^`) in various places, such as `/^this|^that|^something( else)?/`. – Tomalak Jan 26 '15 at 15:29
  • Regex queries do use an index when available. They can use the index efficiently in the case of prefix matches by transforming the query into a range query. Otherwise, MongoDB does a full index scan to check the regex against all of the values of the field. Please see [$regex index use](http://docs.mongodb.org/manual/reference/operator/query/regex/#index-use). – wdberkeley Jan 27 '15 at 00:08

1 Answers1

0

Indexes don't work on regex in mongodb.

Shrinivas Kalangutkar
  • 983
  • 1
  • 9
  • 16