CODEWITHSUNDEEP
regexmongodbindexing
adityap
adityap

Reputation: 739

Mongodb complex regex queries

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 :-(

Upvotes: 0

Views: 703

Answers (1)

Tyler Brock
Tyler Brock

Reputation: 30136

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/
        ]
    ]
}
}

Upvotes: 1

Related Questions