我有一个MondoDB集合,其中包含超过500万个项目.每个项目都有一个包含整数值的开始"和结束"字段.
I have a MondoDB collection with over 5 million items. Each item has a "start" and "end" fields containing integer values.
项目的开头和结尾没有重叠.
Items don't have overlapping starts and ends.
例如这将无效:
{start:100, end:200} {start:150, end:250}我正在尝试查找在开始和结束之间具有给定值的项目
I am trying to locate an item where a given value is between start and end
start <= VALUE <= end以下查询有效,但需要5到15秒才能返回
The following query works, but it takes 5 to 15 seconds to return
db.blocks.find({ "start" : { $lt : 3232235521 }, "end" :{ $gt : 3232235521 }}).limit(1);我添加了以下几项测试指标,但并没有什么改善
I've added the following indexes for testing with very little improvement
db.blocks.ensureIndex({start:1}); db.blocks.ensureIndex({end:1}); //also a compounded one db.blocks.ensureIndex({start:1,end:1});**编辑**
查询中的explain()结果将导致:
The result of explain() on the query results in:
> db.blocks.find({ "start" : { $lt : 3232235521 }, "end" :{ $gt : 3232235521 }}).limit(1).explain(); { "cursor" : "BtreeCursor end_1", "nscanned" : 1160982, "nscannedObjects" : 1160982, "n" : 0, "millis" : 5779, "nYields" : 0, "nChunkSkips" : 0, "isMultiKey" : false, "indexOnly" : false, "indexBounds" : { "end" : [ [ 3232235521, 1.7976931348623157e+308 ] ] } }加快此特定查询的最佳方法是什么?
What would be the best approach to speeding this specific query up?
推荐答案我想compbound索引应该对您来说工作更快:
I guess compbound index should work faster for you:
db.blocks.ensureIndex({start:1, end:1});您还可以使用说明来查看扫描对象的数量,等等,然后选择最佳索引.
You can also use explain to see number of scanned object, etc and choose best index.
如果您正在使用mongodb< 2.0,您需要更新到2.0+,因为那里的索引可以更快地工作. 另外,您可以限制结果以优化查询.
Also if you are using mongodb < 2.0 you need to update to 2.0+, because there indexes work faster. Also you can limit results to optimize query.
更多推荐
在MongoDB中搜索值介于两个项目字段之间的记录
发布评论