title: Optimizing Compound Mongo GeoSpatial Index
body: <p>I have a MongoDB <code>$within</code> that looks like this:</p>
  
  <pre><code>db.action.find( { $and : [
      { actionType : "PLAY" },
      { 
          location : { 
              $within : { 
                  $polygon : [ [ 0.0, 0.1 ], [ 0.0, 0.2 ] .. [ a.b, c.d ] ]
              } 
          }
      }
  ] } ).sort( { time : -1 } ).limit(50)
  </code></pre>
  
  <p>With regard to the action collection documents</p>
  
  <ul>
  <li>There are 5 actionTypes </li>
  <li>The action documents MAY or MAY NOT have a location with a ratio of approximately 70:30 for PLAY actions</li>
  <li>Otherwise there is no location </li>
  <li>The action documents will ALWAYS have time</li>
  </ul>
  
  <p>The collection contains the following indexes</p>
  
  <pre><code># I am interested recent actions 
  db.action.ensureIndex({"time": -1}          
  
  # I am interested in recent actions by a specific user
  db.action.ensureIndex({"userId" : 1}, "time" -1}    
  
  # I am interested in recent actions that relate to a unique song id
  db.action.ensureIndex({"songId" : 1}, "time" -1}    
  </code></pre>
  
  <p>I am experimenting with the following two indexes </p>
  
  <ul>
  <li>LocationOnly: <code>db.action.ensureIndex({"location":"2d"})</code>       </li>
  <li>LocationPlusTime: <code>db.action.ensureIndex({"location":"2d"}, { "time": -1})</code></li>
  </ul>
  
  <p>Identical queries with each index are explained below:</p>
  
  <p><strong>LocationOnly</strong></p>
  
  <pre><code>{
      "cursor":"BasicCursor",
      "isMultiKey":false,
      "n":50,
      "nscannedObjects":91076,
      "nscanned":91076,
      "nscannedObjectsAllPlans":273229,
      "nscannedAllPlans":273229,
      "scanAndOrder":true,
      "indexOnly":false,
      "nYields":1,
      "nChunkSkips":0,
      "millis":1090,
      "indexBounds":{},
      "server":"xxxx"
  }
  </code></pre>
  
  <p><strong>LocationPlusTime</strong></p>
  
  <pre><code>{
      "cursor":"BasicCursor",
      "isMultiKey":false,
      "n":50,
      "nscannedObjects":91224,
      "nscanned":91224,
      "nscannedObjectsAllPlans":273673,
      "nscannedAllPlans":273673,
      "scanAndOrder":true,
      "indexOnly":false,
      "nYields":44,
      "nChunkSkips":0,
      "millis":1156,
      "indexBounds":{},
      "server":"xxxxx"
  }
  </code></pre>
  
  <p>Given </p>
  
  <ul>
  <li>The geosearch will cover documents of ALL types</li>
  <li>The geosearch will cover documents with NO Location and WITH Location in a ratio of roughly 60:40</li>
  </ul>
  
  <p>My questions are</p>
  
  <ul>
  <li>Can anybody explain why isMultiKey="false" on the second explain plan?</li>
  <li>Can anybody explain why there are more yields on the 2nd explain plan?</li>
  </ul>
  
  <p>My speculative thoughts are</p>
  
  <ul>
  <li>The potential for NULL location is reducing the effectiveness of the
  GeoSpatial index. </li>
  <li>Compound Indexes of the GeoSpatial variety are not as powerful as standard compound indexes.</li>
  </ul>
  
  <p><strong>UPDATE</strong>
  A sample document looks like this.</p>
  
  <pre><code>{ "_id" : "adba1154f1f3d4ddfafbff9bb3ae98f2a50e76ffc74a38bae1c44d251db315d25c99e7a1b4a8acb13d11bcd582b9843e335006a5be1d3ac8a502a0a205c0c527", 
    "_class" : "ie.soundwave.backstage.model.action.Action", 
    "time" : ISODate("2013-04-18T10:11:57Z"),
    "actionType" : "PLAY",
    "location" : { "lon" : -6.412839696767714, "lat" : 53.27401934563561 },
    "song" : { "_id" : "82e08446c87d21b032ccaee93109d6be", 
               "title" : "Motion Sickness", "album" : "In Our Heads", "artist" : "Hot Chip"
             }, 
    "userId" : "51309ed6e4b0e1fb33d882eb", "createTime" : ISODate("2013-04-18T10:12:59.127Z") 
  }
  </code></pre>
  
  <p><strong>UPDATE</strong>
  The geo-query looks like this
  <a href="https://www.google.com/maps/ms?msid=214949566612971430368.0004e267780661744eb95&amp;msa=0&amp;ll=-0.01133,-0.019226&amp;spn=0.14471,0.264187" rel="nofollow">https://www.google.com/maps/ms?msid=214949566612971430368.0004e267780661744eb95&amp;msa=0&amp;ll=-0.01133,-0.019226&amp;spn=0.14471,0.264187</a></p>
  
  <p>For various reasons approximately 250,000 documents exist in our DB at the point 0.0</p>