title: MongoDB not using my index
body: <p>I have a logs collection with millions of records. Creating a new index takes "forever". So it would be preferred to use existing indexes.</p>
  
  <p>Now I want to get the number of occurances of certain error codes. I use this query, and functionally it works fine:</p>
  
  <pre><code>db.getCollection('logs.res').aggregate([
      {
         $match:{    
             timeStamp: {
                 $gte: new Date('2017-05-01').getTime(), // timeStamp is Number
                 $lt : new Date('2017-05-02').getTime()  // of ms since epoch
             },
             'objData.@.ErrorCode': {
                 $ne: null
             }
          }
      },
      {
          $group: {
              _id: '$objData.@.ErrorCode',
              count: {$sum: 1}
          }
      },
      {
          $sort: { count: -1}
      }
  ]);
  </code></pre>
  
  <p>The problem is that it takes well near 10 seconds just to execute this for a day. I had assumed the following index would be used: <code>timeStamp_-1_objData.@.ErrorCode_1</code>:</p>
  
  <pre><code>{
      "timeStamp" : -1,
      "objData.@.ErrorCode" : 1
  }
  </code></pre>
  
  <p>However, MongoDB seems adamant to use some <code>timeStamp: 1</code> index (with some other indexes unrelated to the query), and scan alllllll the results to see if some responses might have an <code>ErrorCode</code> attached, even though this information should be in the index.</p>
  
  <p>Here is the <code>explain()</code>:</p>
  
  <p><a href="https://i.stack.imgur.com/K1v37.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/K1v37.png" alt="enter image description here"></a></p>
  
  <ul>
  <li><strong>Is there a way to use the</strong> <code>timeStamp_-1_objData.@.ErrorCode_1</code> <strong>index</strong> to speed this up?</li>
  <li><strong>Why <em>isn't</em> it using this index?</strong> I'm probably misunderstanding how indexes are used in this query.</li>
  </ul>
  
  <hr>
  
  <p>Running MongoDB 3.2.7 on OSX.</p>
  
  <p><strong>note:</strong> I've also tried <code>$empty: true</code> in stead of <code>$ne: null</code>. It yields the same results, but some say you cannot use <code>$empty</code> if you want to use a compound index. Many questions on Stack Overflow are old (mongo 2.x) though.</p>