mongodb排序和正则表达式查询的有效方式 [英] mongodb sort and regex query in efficient way

问题描述

    db.location.find(
     { "$or": [ 
         { "country_lc": /^unit/, "docType": "country" }, 
         { "region_lc": /^unit/, "docType": "region" }, 
         { "city_lc": /^unit/, "docType": "city" } 
    ]}, 
    { "country": 1, "region": 1, "city": 1, "docType" :1 }
   ).sort({ "country_lc" :1, "region_lc": 1, "city_lc":1 })

这是monodb中的查询，需要花费很多时间.如何有效地查询呢?以下是上述查询的explain()输出.我在收集位置总共有442161个文档.我必须做一些前缀搜索.我已经完成了对(country_lc，docType)，(region_lc，docType)，(city_lc，docType)和(country_lc，region_lc，city_lc)的索引.我的mongo版本是2.4.9.

this is query in monodb is taking very much time. How to query this efficiently ? Below is the explain() output of the above query. I have total 442161 documents in the collection location.I have to do some prefix searching.I have done indexing in (country_lc,docType) ,(region_lc,docType),(city_lc,docType) and (country_lc,region_lc,city_lc). My mongo version is 2.4.9.

{
"cursor" : "BtreeCursor country_lc_1_region_lc_1_city_lc_1",
"isMultiKey" : false,
"n" : 29,
"nscannedObjects" : 76935,
"nscanned" : 442161,
"nscannedObjectsAllPlans" : 76935,
"nscannedAllPlans" : 442161,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 79,
"nChunkSkips" : 0,
"millis" : 81531,
"indexBounds" : {
    "country_lc" : [
        [
            {
                "$minElement" : 1
            },
            {
                "$maxElement" : 1
            }
        ]
    ],
    "region_lc" : [
        [
            {
                "$minElement" : 1
            },
            {
                "$maxElement" : 1
            }
        ]
    ],
    "city_lc" : [
        [
            {
                "$minElement" : 1
            },
            {
                "$maxElement" : 1
            }
        ]
    ]
},
"server" : "prashanta:27017"

}

推荐答案

您可以尝试创建

You could try creating a text index on the country_lc, region_lc and city_lc fields:

db.reviews.ensureIndex( { "country_lc": "text" } )
db.reviews.ensureIndex( { "region_lc": "text" } )
db.reviews.ensureIndex( { "city_lc": "text" } )

文本索引是MongoDB 2.4中的一项新功能.添加它们是为了支持对集合文档中字符串内容进行文本搜索.请查看官方文档以获取性能提示.

Text indices are a new feature in MongoDB 2.4. They were added to support text search of string content in documents of a collection. Please take a look at the official documentation for performance hints.

此外，您可以尝试将查询重写为

Moreover, you can give a try at rewriting the query as

db.location.find(
     { "docType": {"$in": [ "country", "region", "city" ]},
       "$or": [
         { "country_lc": /^unit/ },
         { "region_lc": /^unit/ },
         { "city_lc": /^unit/ },
       ]
    }, 
    { "country": 1, "region": 1, "city": 1, "docType" :1 }
   ).sort({ "country_lc" :1, "region_lc": 1, "city_lc":1 })

(警告:这与您的查询是否相同，取决​​于文档的结构.)

(Caution: This is, or isn't, equivalent to your query, depending on the structure of the documents.)

这篇关于mongodb排序和正则表达式查询的有效方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！