title: MongoDB query is slow and not using Indexes
body: <p>I have a collection with structure like this:</p>
  
  <pre><code>{
      "_id" : ObjectId("59d7cd63dc2c91e740afcdb"),
      "enrollment" : [
          { "month":-10, "enroll":'00'},
          { "month":-9, "enroll":'00'},
          { "month":-8, "enroll":'01'},
          //other months
          { "month":8, "enroll":'11'},
          { "month":9, "enroll":'11'},
          { "month":10, "enroll":'00'}
      ]
  }
  </code></pre>
  
  <p>I am trying to run the following query:</p>
  
  <pre><code>db.getCollection('collection').find({ 
            "enrollment": { 
                "$not": { 
                   "$elemMatch": { "month": { "$gte": -2, "$lte": 9 }, "enroll": "00" } 
                 } 
             } 
         }).count()
  </code></pre>
  
  <p>This query is taking 1.6 to 1.9 seconds. I need to get this down as low as possible, to milli seconds if that is possible.</p>
  
  <p>I tried creating multi key index on month and enrolled fields. I tried various combinations but the query is not using any indexes.</p>
  
  <p>I tried all these combinations:</p>
  
  <pre><code>1. { 'enrollment.month':1 }
  2. { 'enrollment.month':1 }, { 'enrollment.enroll':1 } -- two seperate indexes
  3. { 'enrollment.month':1, 'enrollment.enroll':1} 
  4. { 'enrollment.enroll':1, 'enrollment.month':1}
  </code></pre>
  
  <p>Parsed Query:</p>
  
  <p><a href="https://i.stack.imgur.com/TM1s9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TM1s9.png" alt="enter image description here"></a></p>
  
  <p>Query Plan: </p>
  
  <p><a href="https://i.stack.imgur.com/P102n.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/P102n.png" alt="enter image description here"></a></p>
  
  <p>Any suggestions to improve the performance are highly appreciated.</p>
  
  <p>I am fairly confident that the hardware is not an issues but open for any suggestions.</p>
  
  <p>My data size is not huge. Its just under 1GB. Total number of documents are 41K and sub document count is approx. 13 million</p>
  
  <p>Note: I have posted couple of questions on this in last few days, but with this i am trying to narrow down the area. Please do not take this as a duplicate of my earlier questions.</p>