Mongodb query by regular expression

Question

I use Mongodb to store list of locations over the world, with more than 2M records. Each record is an object like this:

{ "_id" : ObjectId("4e5b339feee76320ab26f930"), "city" : "New York", "longitude" : -87.2008333, "latitude" : 30.8383333, "country_code" : "US", "country_name" : "United States" }

I want to perform the search to get out all "CITIES" contain "New York", it took me about 10 seconds to have the result (it is unacceptable in my web system). I have indexed the "city" using ensureIndex() function, but the query is still slow.

Here is my query:

db.locations.find({"city": { "$regex": "(New York)", "$options": 'i' }})

I guess the problem is the "regular expression". Can you suggest me a solution for this to get the query result within 2-3 seconds (I have more than 4M records in MySQL, the similar query took me only 1-2 seconds - with indexes).

Thanks and regards.

Andrew Orsich · Accepted Answer · 2011-08-29 09:29:51Z

8

You can't search with contain operation in mongodb without using regexp or javascript (they are slow, because of work without index).

I can suggest to store additional city in lower case and search by full match. If you want 'contains' and fast speed you should use some another full text search engines like solr or lucene.

answered Aug 29, 2011 at 9:29

Andrew Orsich

53.8k17 gold badges142 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

iMysak · Accepted Answer · 2011-08-29 11:40:39Z

3

I recommends use multi keys.

example:

{ title : "this is fun" ,
  _keywords : [ "this" , "is" , "fun" ]
}

then you can use

 db.articles.findOne( { _keywords: "this" } )

this will be more faster

answered Aug 29, 2011 at 11:40

iMysak

2,2362 gold badges23 silver badges35 bronze badges

Comments

Dmitrii Sidenko · Accepted Answer · 2019-06-26 12:18:55Z

2

Mongo doesn't use index for regexp when it search with case insensitive. I suggest you to store your field with uppercase or lowercase and use same for search.

Instead of search containing if you search start with like below

db.locations.find({"city": { "$regex": /^New York/}})

your query will return fast .

for more info RegularExpressions

edited Jun 26, 2019 at 12:18

Dmitrii Sidenko

6686 silver badges20 bronze badges

answered Aug 29, 2011 at 13:07

Swapnil Sonawane

1,4553 gold badges22 silver badges37 bronze badges

Collectives™ on Stack Overflow

Mongodb query by regular expression

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related