0

I have a query that may be too complicated for MongoDB to execute it quickly. If there are things that I can change in how my MongoDB is setup, I'd love to know!

Here is an example query that is search the jobs collection for the term "api"

db.jobs.find(
  {
    'budget': {'$exists': true, '$ne': ''}, 
    '$or': [
      {'title': /(^|[\\s.,])api.*/gi}, 
      {'query': /api'/gi}, 
      {'description': /(^|[\\s.,])api.*/gi}
    ]
  }
)

This ends up taking more than 3 seconds to run across 32000 documents (excerpt from MongoDB Profiler):

"ntoreturn" : 0,
"ntoskip" : 0,
"nscanned" : 0,
"nscannedObjects" : 34266,
"keyUpdates" : 0,
"writeConflicts" : 0,
"numYield" : 274,

I was thinking of adding an index on either title, query, description, but MongoDB only allows one text index per collection, while I have three string fields (title, query, description) that I am querying.

shA.t
  • 16,580
  • 5
  • 54
  • 111
Jim Factor
  • 1,465
  • 1
  • 15
  • 24
  • you could replace the alternation with `\b` ? also you should reverse it `[\s.,]|^` most likely, since the first case should be the most usual one. – Scott Weaver Jan 13 '17 at 23:19
  • 2
    you can create a text index over any number of fields. `db.jobs.createIndex( { title: "text", query: "text", description: "text" } )`. Try & see if it improves your query response time. – s7vr Jan 14 '17 at 01:56
  • 1
    IMO, You need to review your Regexes. And a [related question](http://stackoverflow.com/q/17501798/4519059) ;). – shA.t Jan 14 '17 at 04:00

1 Answers1

0

The solution was to create a text index, based off of 3 fields. I didn't know Mongo allowed you to use multiple fields per text index. Going super fast now!

Jim Factor
  • 1,465
  • 1
  • 15
  • 24