CODEWITHSUNDEEP
mongodboptimizationdatabase
Pawel Veselov
Pawel Veselov

Reputation: 4225

Searching for ranges in mongo

What's the most efficient way to find data in Mongo, when the input data is a single value, and the collection data contains min/max ranges? E.g:

record = { min: number, max: number, payload }

Need to locate a record for a number that falls within the min/max range of the record. The ranges never intersect. There is no predictability about the size of the ranges.

The collection has ~6M records in it. If I unpack the ranges (have records for each value in range), I would be looking at about 4B records instead.

I've created the compound index of {min:1,max:1}, but attempt to search using:

db.block.find({min:{$lte:value},max:{$gte:value})

... takes anywhere from few to tens of seconds. Below are the output of explain() and getIndexes(). Is there any trick I can apply to make the search execute significantly faster?

NJmongo:PRIMARY> db.block.getIndexes()
[
    {
            "v" : 1,
            "key" : {
                    "_id" : 1
            },
            "ns" : "mispot.block",
            "name" : "_id_"
    },
    {
            "v" : 1,
            "key" : {
                    "min" : 1,
                    "max" : 1
            },
            "ns" : "mispot.block",
            "name" : "min_1_max_1"
    }
] 


NJmongo:PRIMARY> db.block.find({max:{$gte:1135194602},min:{$lte:1135194602}}).explain()
{
    "cursor" : "BtreeCursor min_1_max_1",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 1199049,
    "nscannedObjectsAllPlans" : 1199050,
    "nscannedAllPlans" : 2398098,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 7534,
    "nChunkSkips" : 0,
    "millis" : 5060,
    "indexBounds" : {
            "min" : [
                    [
                            -1.7976931348623157e+308,
                            1135194602
                    ]
            ],
            "max" : [
                    [
                            1135194602,
                            1.7976931348623157e+308
                    ]
            ]
    },
    "server" : "ccc:27017"
}

Upvotes: 2

Views: 588

Answers (1)

Leopd
Leopd

Reputation: 42757

If the ranges of your block records never overlap, then you can accomplish this much faster with:

db.block.find({min:{$lte:value}}).sort({min:-1}).limit(1)

This query will return almost instantly since it can find the record with a simple lookup in the index.

The query you are running is slow because the two clauses each match on millions of records that must be merged. In fact, I think your query would run faster (maybe much faster) with separate indexes on min and max since the max part of your compound index can only be used for a given min -- not to search for documents with a specific max.

Upvotes: 1

Related Questions