How to optimize mongoDB query?

Question

I have the following sample document in 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"
}

Note:

• The database contains more 10845document
• Each document contains almost a 100request (100 objects in an array of requests).

• Technology / Language - node.js

• I had setProfilingto check the runtime

  First Query - 13899ms
  Second Query - 9024ms 
  Third Query - 8310ms
  Fourth Query - 6858ms
  

• There is no difference in using indexing

Requests:

I have the following aggregation queriesto execute data.

 var match = {"request.currentVisit":{$gte:core.getTime()[1].toString(),$lte:core.getTime()[0].toString()}};

For Example: var match = {"request.currentVisit": {$ gte: "1401282905", $ lte: "1401282935"}};

For the third and fourth queries request.visitedTimeinsteadrequest.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} }
  ]
  

• Thirdly

  [
      { "$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" }
     }}
  ]
  

Problem:

To obtain data requires more than 38091 ms.

Is there a way to optimize the query?

Any suggestion would be appreciated.

Answer 1

, , , . "timestamp", , . , , BSON. , , , .

, "" $unwind, "" , . , , , , , $unwind. "" , . .

, start . "" , .

, :

[
   { "$match":{
       { "request.currentVisit":{ 
           "$gte":"1401282905", "$lte": "1401282935"
       }
   }},
   { "$unwind": "$request" },
   { "$match":{
       { "request.currentVisit":{ 
           "$gte":"1401282905", "$lte": "1401282935"
       }
   }},
   { "$group": { 
       "_id": {
           "url":"$request.url"
       },
       "count": { "$sum": 1 }
   }},
   { "$sort":{ "count": -1 } }
]

, . $match. . . , " ".

$project, , , "" , . , , $project . , $project $group, , " ", . , $match .

, $match, "" . , .

, $unwind $match, , . , .

, MongoDB 2.6 , "" , ** $unwind it, :

[
   { "$match":{
       { "request.currentVisit":{ 
           "$gte":"1401282905", "$lte": "1401282935"
       }
   }},
   { "$project": {
       "request": {
           "$setDifference": [
               { 
                   "$map": {
                       "input": "$request",
                       "as": "el",
                       "in": {
                           "$cond"": [
                               {
                                   "$and":[
                                       { "$gte": [ "1401282905", "$$el.currentVisit" ] },
                                       { "$lt": [ "1401282935", "$$el.currentVisit" ] }
                                   ]
                               }
                               "$el",
                               false
                           ]
                       }
                   }
               }
               [false]
           ]
       }
   }}
   { "$unwind": "$request" },
   { "$group": { 
       "_id": {
           "url":"$request.url"
       },
       "count": { "$sum": 1 }
   }},
   { "$sort":{ "count": -1 } }
]

, "" $unwind , , $match.

. , $match .

, , , , , . , , .

, , .