Mongodb complex regex queries

Question

I have collection of cities like this

{ "name": "something","population":2121}

there are thousands of documents like this in one collection

now, I have created index like this

$coll->ensureIndex(array("name" => 1, "population" => -1), 
                   array("background" => true));

now I want to query like this

$cities = $coll->find(array("name" => array('$regex' => "^$name")))
              ->limit(30)
              ->sort(array("name" => 1, "population" => -1));

But this returns cities in ascending order of population. But I want result as descending order of population i.e. highest population first.

Any idea???

EDIT: I have created individual indexes on name and population. Following is output of db.city_info.getIndexes() and db.city_info.find({ "name": { "$regex": "^Ban" } }).sort({ "population": -1 }).explain(); respectively

[
{
    "v" : 1,
    "key" : {
        "_id" : 1
    },
    "ns" : "city_database.city_info",
    "name" : "_id_"
},
{
    "v" : 1,
    "key" : {
        "name" : 1
    },
    "ns" : "city_database.city_info",
    "background" : 1,
    "name" : "ascii_1"
},
{
    "v" : 1,
    "key" : {
        "population" : 1
    },
    "ns" : "city_database.city_info",
    "background" : 1,
    "name" : "population_1"
}
]

and

{
"cursor" : "BtreeCursor ascii_1 multi",
"nscanned" : 70739,
"nscannedObjects" : 70738,
"n" : 70738,
"scanAndOrder" : true,
"millis" : 17461,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
    "name" : [
        [
            "Ban",
            "Bao"
        ],
        [
            /^Ban/,
            /^Ban/
        ]
    ]
}
}

Just look at time taken by query :-(

Sorry, now I have posted the right thing. that asciiname was mistake. It is name only... — adityap
– adityap, Commented Jan 9, 2012 at 11:35
Sample data {"name":"London", "population":121212} {"name":"New York","population":1212232} — adityap
– adityap, Commented Jan 9, 2012 at 11:37

Tyler Brock · Accepted Answer · 2012-01-13 09:22:20Z

1

If you want the results to be in descending order of population (greatest to least) then remove the sort on name within the query.

my is too short has the right idea

When you sort on name and then descending population, what you have now, it sorts by name, which is most likely unique-ish because we are talking about cities, and then by population.

Also, make sure you have an index on name:

db.cities.ensureIndex({population: 1})

Direction doesn't matter when the index is on one field.

Update (sample of similar index, query and explain):

> db.test.insert({name: "New York", population: 5000})
> db.test.insert({name: "Longdon", population: 7000})
> db.test.ensureIndex({name: 1})
> db.test.find({name: {"$regex": "^New"}}).sort({poplation: -1})
{ "_id" : ObjectId("4f0ff70072999b69b616d2b6"), "name" : "New York", "population" : 5000 }
> db.test.find({name: {"$regex": "^New"}}).sort({poplation: -1}).explain()
{
"cursor" : "BtreeCursor name_1 multi",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"scanAndOrder" : true,
"millis" : 1,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
    "name" : [
        [
            "New",
            "Nex"
        ],
        [
            /^New/,
            /^New/
        ]
    ]
}
}

edited Jan 13, 2012 at 9:22

answered Jan 9, 2012 at 18:00

Tyler Brock

30.3k16 gold badges82 silver badges82 bronze badges

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

10 Comments

adityap Over a year ago

if I remove sort on name then it does not use indexes. :-(

Tyler Brock Over a year ago

That is because you need to create an index on population only, the one you created on name and population is not helpful for the correct query.

adityap Over a year ago

if I dont create index on name then that regex query will not use index. DO you mean I should create different index for name and population?

Tyler Brock Over a year ago

I got similar output from explain (see above update). However it seems that your nscanned is very high, are you sure that your regex is not matching a ton of the documents? What version are you running? How many documents are in the collection. It might just be that you don't have enough memory to hold the working set.

Tyler Brock Over a year ago

How much ram do you have? What is the size of the collection? db.cities.stats() -- please post output above

|

Collectives™ on Stack Overflow

Mongodb complex regex queries

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related