Optimizing mongodb date range queries
I need to find items that are in or overlap with a date range.
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