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.

Answer 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.