title: How to use $hint in MongoDB aggregation query?
body: <p>I am using mongo v3.0.1 on a ubuntu machine. And I have a collection of 300million rows. I have created two indexes based on my query preference.</p>
  
  <p>When I am trying to run aggregation with explain, It is taking the inefficient index, and that is why it's taking 20-25 secs more time. Is there any way to put <code>$hint</code>, so that my aggregation query use the appropriate  index. </p>
  
  <p><code>$match</code> is in my first pipeline stage. I have two indexes: </p>
  
  <ol>
  <li><p>"Host_-1_SiteType_-1"</p></li>
  <li><p>"VisitTime_-1_AccountId_-1_Host_-1_SiteType_-1_Extension_-1_LifeTime_-1"</p></li>
  </ol>
  
  <p>and my <code>$match</code> pipeline is like :</p>
  
  <pre class="lang-js prettyprint-override"><code>{ "$match" : {
      "AccountId": accID, 
      "VisitTime": { "$lte" : today, "$gte" : last365Days },
      "$or": [
          { "$and": [
              { "Extension":{ "$in": ["chrome_0","firefox_0"] }},
              { "LifeTime": 0 }
          ]},
          {  "LifeTime": { "$gt": 1000 }}
      ],
      "Host": { "$ne": "localhost" },
      "SiteType" : { "$exists": true },
  }
  </code></pre>
  
  <p>and it is using first index, instead of second index. and the time taken by the first index in 50 secs where as using second index only it is taking only 18 secs.</p>
  
  <p>Here is my one of the document sample: </p>
  
  <pre class="lang-js prettyprint-override"><code>{ 
      "_id" : "2bc1143c-07e4-4c37-a020-a7485b2802a3", 
      "CreatedDate" : ISODate("2015-07-22T04:05:06.802+0000"), 
      "UpdatedDate" : ISODate("2015-07-22T05:28:26.469+0000"), 
      "AccountId" : accID, 
      "Url" : "http://www.test.com/test.html", 
      "Host" : "test.com", 
      "VisitTime" : ISODate("2014-08-12T18:08:25.813+0000"), 
      "LifeTime" : 789546.01, 
      "Status" : "closed", 
      "LocalTime" : ISODate("2014-08-12T18:08:25.813+0000"), 
      "DeviceId" : "123456789", 
      "Extension" : "firefox_0", 
      "SubSiteType" : "TestSubSite", 
      "SiteType" : "TestSite", 
      "Flag" : "1"
  }
  </code></pre>
  
  <p>and here is my aggregation explanation: </p>
  
  <pre class="lang-js prettyprint-override"><code>{
      "stages" : [
          {
              "$cursor" : {
                  "query" : {
                      "AccountId" : "accID",
                      "VisitTime" : {
                          "$lte" : "2015-07-25T18:30:00Z",
                          "$gte" : "2014-07-25T18:30:00Z"
                      },
                      "Host" : {
                          "$ne" : "localhost"
                      },
                      "SiteType" : {
                          "$exists" : true
                      },
                      "$or" : [
                          {
                              "$and" : [
                                  {
                                      "Extension" : {
                                          "$in" : [
                                              "chrome_0",
                                              "firefox_0"
                                          ]
                                      }
                                  },
                                  {
                                      "LifeTime" : 0
                                  }
                              ]
                          },
                          {
                              "LifeTime" : {
                                  "$gt" : 1000
                              }
                          }
                      ]
                  },
                  "fields" : {
                      "Host" : 1,
                      "_id" : 0
                  },
                  "queryPlanner" : {
                      "plannerVersion" : 1,
                      "namespace" : "Test",
                      "indexFilterSet" : false,
                      "parsedQuery" : {
                          "$and" : [
                              {
                                  "$or" : [
                                      {
                                          "$and" : [
                                              {
                                                  "LifeTime" : {
                                                      "$eq" : 0
                                                  }
                                              },
                                              {
                                                  "Extension" : {
                                                      "$in" : [
                                                          "chrome_0",
                                                          "firefox_0"
                                                      ]
                                                  }
                                              }
                                          ]
                                      },
                                      {
                                          "LifeTime" : {
                                              "$gt" : 1000
                                          }
                                      }
                                  ]
                              },
                              {
                                  "$not" : {
                                      "Host" : {
                                          "$eq" : "localhost"
                                      }
                                  }
                              },
                              {
                                  "VisitTime" : {
                                      "$lte" : "2015-07-25T18:30:00Z"
                                  }
                              },
                              {
                                  "AccountId" : {
                                      "$eq" : "accID"
                                  }
                              },
                              {
                                  "VisitTime" :"2014-07-25T18:30:00Z"
  
                              },
                              {
                                  "SiteType" : {
                                      "$exists" : true
                                  }
                              }
                          ]
                      },
                      "winningPlan" : {
                          "stage" : "FETCH",
                          "filter" : {
                              "$and" : [
                                  {
                                      "SiteType" : {
                                          "$exists" : true
                                      }
                                  },
                                  {
                                      "$or" : [
                                          {
                                              "$and" : [
                                                  {
                                                      "LifeTime" : {
                                                          "$eq" : 0
                                                      }
                                                  },
                                                  {
                                                      "Extension" : {
                                                          "$in" : [
                                                              "chrome_0",
                                                              "firefox_0"
                                                          ]
                                                      }
                                                  }
                                              ]
                                          },
                                          {
                                              "LifeTime" : {
                                                  "$gt" : 1000
                                              }
                                          }
                                      ]
                                  },
                                  {
                                      "VisitTime" : {
                                          "$lte" : "2015-07-25T18:30:00Z"
                                      }
                                  },
                                  {
                                      "AccountId" : {
                                          "$eq" : "accID"
                                      }
                                  },
                                  {
                                      "VisitTime" : {
                                          "$gte" : "2014-07-25T18:30:00Z"
                                      }
                                  }
                              ]
                          },
                          "inputStage" : {
                              "stage" : "IXSCAN",
                              "keyPattern" : {
                                  "Host" : -1,
                                  "SiteType" : -1
                              },
                              "indexName" : "Host_-1_SiteType_-1",
                              "isMultiKey" : false,
                              "direction" : "forward",
                              "indexBounds" : {
                                  "Host" : [
                                      "[MaxKey, \"localhost\")",
                                      "(\"localhost\", MinKey]"
                                  ],
                                  "SiteType" : [
                                      "[MaxKey, MinKey]"
                                  ]
                              }
                          }
                      },
                      "rejectedPlans" : [
                          {
                              "stage" : "FETCH",
                              "filter" : {
                                  "$and" : [
                                      {
                                          "SiteType" : {
                                              "$exists" : true
                                          }
                                      },
                                      {
                                          "$or" : [
                                              {
                                                  "$and" : [
                                                      {
                                                          "LifeTime" : {
                                                              "$eq" : 0
                                                          }
                                                      },
                                                      {
                                                          "Extension" : {
                                                              "$in" : [
                                                                  "chrome_0",
                                                                  "firefox_0"
                                                              ]
                                                          }
                                                      }
                                                  ]
                                              },
                                              {
                                                  "LifeTime" : {
                                                      "$gt" : 1000
                                                  }
                                              }
                                          ]
                                      }
                                  ]
                              },
                              "inputStage" : {
                                  "stage" : "IXSCAN",
                                  "keyPattern" : {
                                      "VisitTime" : -1,
                                      "AccountId" : -1,
                                      "Host" : -1,
                                      "SiteType" : -1,
                                      "Extension" : -1,
                                      "LifeTime" : -1
                                  },
                                  "indexName" : "VisitTime_-1_AccountId_-1_Host_-1_SiteType_-1_Extension_-1_LifeTime_-1",
                                  "isMultiKey" : false,
                                  "direction" : "forward",
                                  "indexBounds" : {
                                      "VisitTime" : [
                                          "[new Date(1437849000000), new Date(1406313000000)]"
                                      ],
                                      "AccountId" : [
                                          "[\"accID\", \"accID\"]"
                                      ],
                                      "Host" : [
                                          "[MaxKey, \"localhost\")",
                                          "(\"localhost\", MinKey]"
                                      ],
                                      "SiteType" : [
                                          "[MaxKey, MinKey]"
                                      ],
                                      "Extension" : [
                                          "[MaxKey, MinKey]"
                                      ],
                                      "LifeTime" : [
                                          "[MaxKey, MinKey]"
                                      ]
                                  }
                              }
                          }
                      ]
                  }
              }
          },
          {
              "$group" : {
                  "_id" : "$Host",
                  "Count" : {
                      "$sum" : {
                          "$const" : 1
                      }
                  }
              }
          },
          {
              "$sort" : {
                  "sortKey" : {
                      "Count" : -1
                  },
                  "limit" : 5
              }
          },
          {
              "$project" : {
                  "_id" : false,
                  "Host" : "$_id",
                  "TotalVisit" : "$Count"
              }
          }
      ],
      "ok" : 1
  }
  </code></pre>