我在MongoDB中有以下示例文档。
{
"location" : {
"language" : null,
"country" : "null",
"city" : "null",
"state" : null,
"continent" : "null",
"latitude" : "null",
"longitude" : "null"
},
"request" : [
{
"referrer" : "direct",
"url" : "http://www.google.com/"
"title" : "index page"
"currentVisit" : "1401282897"
"visitedTime" : "1401282905"
},
{
"referrer" : "direct",
"url" : "http://www.stackoverflow.com/",
"title" : "index page"
"currentVisit" : "1401282900"
"visitedTime" : "1401282905"
},
......
]
"uuid" : "109eeee0-e66a-11e3"
}
请注意:
- The database contains more than
10845
document - Each document contains nearly
100
request(100 object in the request array). Technology/Language -
node.js
I had
setProfiling
to check the execution timeFirst Query - 13899ms Second Query - 9024ms Third Query - 8310ms Fourth Query - 6858ms
There is no much difference using indexing
查询:
我需要执行以下聚合查询
以获取数据。
var match = {"request.currentVisit":{$gte:core.getTime()[1].toString(),$lte:core.getTime()[0].toString()}};
例如:var match = {"request.currentVisit":{$gte:"1401282905",$lte:"1401282935"}}; 对于第三和第四个查询,请使用"request.visitedTime"代替"request.currentVisit"。
First
[ { "$project":{ "request.currentVisit":1, "request.url":1 }}, { "$match":{ "request.1": {$exists:true} }}, { "$unwind": "$request" }, { "$match": match }, { "$group": { "_id": { "url":"$request.url" }, "count": { "$sum": 1 } }}, { "$sort":{ "count": -1 } } ]
Second
[ { "$project": { "request.currentVisit":1, "request.url":1 }}, { "$match": { "request":{ "$size": 1 } }}, { "$unwind": "$request" }, { "$match": match }, { "$group": { "_id":{ "url":"$request.url" }, "count":{ "$sum": 1 } }}, { "$sort": { "count": -1} } ]
Third
[ { "$project": { "request.visitedTime":1, "uuid":1 }}, { "$match":{ "request.1": { "$exists": true } }}, { "$match": match }, { "$group": { "_id": "$uuid", "count":{ "$sum": 1 } }}, { "$group": { "_id": null, "total": { "$sum":"$count" }} }} ]
Forth
[ { "$project": { "request.visitedTime":1, "uuid":1 }}, { "$match":{ "request":{ "$size": 1 } }}, { "$match": match }, { "$group": { "_id":"$uuid", "count":{ "$sum": 1 } }}, { "$group": { "_id":null, "total": { "$sum": "$count" } }} ]
问题:
获取数据需要 38091 ms
的时间。
是否有优化查询的方法?
欢迎提出建议。