Optimizing mongodb date range queries

I need to find items that are in or overlap with a date range.

enter image description here

I first tried a simple $ or query

db.collection.find({
    $nor: [{
        "start.date": { $lt: start },
        "end.date": { $lt: start }
    }, {
        "start.date": { $gte: end },
        "end.date": { $gte: end }
    }]
});

      

and set the index for both date fields

db.collection.ensureIndex ({"start.date": 1, "end.date": 1});

but it won't use the index in the query

"cursor": "BasicCursor",
"isMultiKey": false,
"n": 2,
"nscannedObjects": 100000,
"nscanned": 100000,
"nscannedObjectsAllPlans": 100000,
"nscannedAllPlans": 100000,
"scanAndOrder": false,
"indexOnly": false,
"nYields": 781,
"nChunkSkips": 0,
"millis": 151,
"allPlans": [
  {
    "cursor": "BasicCursor",
    "isMultiKey": false,
    "n": 2,
    "nscannedObjects": 100000,
    "nscanned": 100000,
    "scanAndOrder": false,
    "indexOnly": false,
    "nChunkSkips": 0
  }
],
"type": "COLLSCAN",

      

another way i tried is to use $ or with the same index

db.collection.find({
    $or: [{
        "start.date": start,
        "end.date": end
    }, {
        "start.date": { $lt: start },
        "end.date": { $lt: end, $gt: start },
    }, {
        "start.date": { $gt: start, $lt: end },
        "end.date": { $gt: end }
    }, {
        "start.date": { $gt: start },
        "end.date": { $lt: end }
    }, {
        "start.date": { $lt: start },
        "end.date": { $gt: end }
    }]
});

      

It will use the index to query, but $ is even faster.

Is there a way to optimize the query or is it possible to use indexes in $ nor (does this make sense anyway?)?

+3


source to share


1 answer


You're looping through elements that start somewhere before end

and end somewhere after start

(which is similar to how you express it with nor

, just a little simpler):



db.collection.find({
    "start.date": { $lt: end },
    "end.date": { $gt: start }
});

      

+4


source







All Articles