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.

Solution 1

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.

Solution 2

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

Solution 3

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