在MongoDB中搜索值介于两个项目字段之间的记录 [英] Search for a record where a value is between two item fields in MongoDB

问题描述

我有一个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中搜索值介于两个项目字段之间的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！