Mongo aggregate query
* not indexed * *
> var startTime = new Date ()
> db.temMinlog.find ({"id": 0}) .count ()
1215
> (new Date () .getTime ()-startTime.getTime ()) / 1000
194.811s
* indexed * *
> db.temMinlog.ensureIndex ({"_ id": 1, "id": 1})
{
"createdCollectionAutomatically": false
"numIndexesBefore": 1
"numIndexesAfter": 2
"ok": 1
}
* query after indexing * *
> var startTime = new Date ()
> db.temMinlog.find ({"id": 0}) .count ()
1215
> (new Date () .getTime ()-startTime.getTime ()) / 1000
227.247s
Time range retrieval
Db.temMinlog.find ({"_ id": {$gte:1498703926535,$lte:1498704749897}, "id": 0}) .explain ("executionStats")
Result "executionStats": {
"executionSuccess": true
"nReturned": 52
"executionTimeMillis": 9506
"totalKeysExamined": 518000
"totalDocsExamined": 52
"executionStages": {
"stage": "FETCH"
"nReturned": 52
"executionTimeMillisEstimate": 2175
"works": 518001
"advanced": 52
"needTime": 517947
"needYield": 0
"saveState": 8151
"restoreState": 8151
"isEOF": 1
"invalidates": 0
"docsExamined": 52
"alreadyHasObj": 0
"inputStage": {
"stage": "IXSCAN"
"nReturned": 52
"executionTimeMillisEstimate": 2145
Fuzzy query
Db.temMinlog.find ({"_ id": {$gte:1498703926535,$lte:1498704749897}, "id": / 0 /}) .explain ("executionStats")
Result "executionStats": {
"executionSuccess": true
"nReturned": 1
"executionTimeMillis": 9893
"totalKeysExamined": 518001
"totalDocsExamined": 1
"executionStages": {
"stage": "FETCH"
"nReturned": 1
"executionTimeMillisEstimate": 2237
"works": 518002
"advanced": 1
"needTime": 517999
"needYield": 0
"saveState": 8151
"restoreState": 8151
"isEOF": 1
"invalidates": 0
"docsExamined": 1
"alreadyHasObj": 0
"inputStage": {
"stage": "IXSCAN"
"filter": {
"id": {
"$regex": "0"
}
}
"nReturned": 1
"executionTimeMillisEstimate": 2185
Fuzzy count ()
> var startTime = new Date ()
> db.temMinlog.find ({"_ id": {$gte:1498703926535,$lte:1498704749897}, "id": {$in: [1meme 2jue 3]}}) .count ()
one hundred and fifty six
> (new Date () .getTime ()-startTime.getTime ()) / 1000
11.232s
* group by statistics * *
Db.temMinlog.aggregate ([{$group: {_ id: "$id", num_tutorial: {$sum:1}}])
Db.temMinlog.aggregate ([{$group: {_ id: "$id", max_v0: {$max: "$v0"}, min_v1: {$min: "$v1"}, count: {$sum:1}}], {allowDiskUse: true})
Db.temMinlog.aggregate ([{$group: {_ id: "$id", max_v0: {$max: "$v0"}, min_v1: {$min: "$v1"}, avg_v2: {$avg: "$v2"}, count: {$sum:1}}], {allowDiskUse: true})
Db.temMinlog.aggregate ([{$match: {_ id: {$gte:1498703926535,$lte:1498704749897}}, {$group: {_ id: "$id", max_v0: {$max: "$v0"}, min_v1: {$min: "$v1"}, avg_v2: {$avg: "$v2"}, sum_v2: {$sum: "$v2"}, count: {$sum:1}}], {allowDiskUse: true})
Results: 12.194s
Db.temMinlog.aggregate ([{$match: {_ id: {$gte:1498703926535,$lte:1498704749897}}, {$limit:5}, {$group: {_ id: "$id", max_v0: {$max: "$v0"}, min_v1: {$min: "$v1"}, avg_v2: {$avg: "$v2"}, sum_v2: {$sum: "$v2"}, count: {$sum:1}}, {$sort: {_ id:-1}}], {allowDiskUse: true})
$match: matching criteria, optional
$limit: number of result entries, optional
$group: aggregation rules
$sort: sort the query results, and-1 indicates descending order
Db.temMinlog.aggregate ([{$match: {_ id: {$gte:1498703926535,$lte:1498704749897}}, {$group: {_ id: "$id", max_v0: {$max: "$v0"}, min_v1: {$min: "$v1"}, avg_v2: {$avg: "$v2"}, sum_v2: {$sum: "$v2"}, count: {$sum:1}}, {$limit:5}, {$sort: {_ id:-1}}])
Results: 21.048s
