title: MongoDB Overflow sort stage on text search
body: <p>Using <a href="http://www.mongodb.org/">MongoDB</a> v2.6, it's not uncommon that if one is sorting a <a href="http://docs.mongodb.org/manual/core/cursors/">cursor</a> from a large result set to get an <a href="https://jira.mongodb.org/browse/SERVER-14142">overflow</a>.</p>
  
  <pre><code>cursor = db.collection.find( { "key" : "value" } )
  cursor.sort( { "rank" : 1 } )  // This can blow up
  </code></pre>
  
  <p>The error looks much like:</p>
  
  <blockquote>
    <p><strong>Runner error: Overflow sort stage buffered data usage of 33598393 bytes exceeds internal limit of 33554432 bytes</strong></p>
  </blockquote>
  
  <p>The solution, in this case, is to <a href="http://docs.mongodb.org/manual/reference/method/db.collection.ensureIndex/#db.collection.ensureIndex">provide an index</a> for the sort criteria, not just the key.</p>
  
  <pre><code>db.collection.ensureIndex( { "rank" : 1 } )  // ascending
  </code></pre>
  
  <p>And this works beautifully. 
  <br><br></p>
  
  <hr>
  
  <p><strong>I'm running into this problem in a different place, a <a href="http://docs.mongodb.org/manual/core/index-text/">text index</a>.</strong>  Following the directions in the MongoDB Manual on <a href="http://docs.mongodb.org/manual/tutorial/create-text-index-on-multiple-fields/">text index creation</a>, I've done precisely this:</p>
  
  <pre><code>db.collection.ensureIndex(
     { "$**": "text" },
     { name: "TextIndex" }
  )
  </code></pre>
  
  <p>And, this has created a text index across all fields on all my <a href="http://docs.mongodb.org/manual/reference/mongodb-extended-json/">ExtendedJSON</a> objects in the collection.</p>
  
  <p>Searching works perfectly.</p>
  
  <pre><code>cursor = db.collection.find( { "$text" : { "$search" : "NEEDLE" } } )
  cursor.count()                      // w00t!  records that have NEEDLE in them
  </code></pre>
  
  <p>However, <strong>attempting to do the same sort that worked before now fails</strong>, even if an index exists for the sort field:</p>
  
  <pre><code>db.collection.ensureIndex( { "rank" : 1 } )  
  cursor = db.collection.find( { "$text" : { "$search" : "NEEDLE" } } )
  cursor.sort( { "rank" : 1 } )      // This blows up with the same error message
  </code></pre>
  
  <blockquote>
    <p><strong>Runner error: Overflow sort stage buffered data usage of 33598393 bytes exceeds internal limit of 33554432 bytes</strong></p>
  </blockquote>
  
  <p><br></p>
  
  <p><em>Here's the weird part.</em></p>
  
  <p>Iterating over the cursor <em>without</em> performing the sort works fine, that's how I get count above.  I can even step through the cursor and see the <em>unordered</em> results, so the text search clearly works.</p>
  
  <p>However, <em>omitting</em> the text search cause the sort to work just fine; this makes me think it isn't quantity based, though I know it's really just using the sort key's index.</p>
  
  <pre><code>db.collection.ensureIndex( { "rank" : 1 } )  
  cursor = db.collection.find( )     // Get absolutely everything
  cursor.sort( { "rank" : 1 } )      // Well, sort now works again... hmm....
  </code></pre>
  
  <p><strong>Just as I had to "help" Mongo by providing an index so it could sort without bring all the records into memory, how does one accomplish this for text indexes?</strong></p>
  
  <p><em>Unfortuantely, I can't get an <a href="http://docs.mongodb.org/manual/reference/method/cursor.explain/">explain plan</a> as it too yields the same error.  If I do it on the results of the <a href="http://docs.mongodb.org/manual/reference/method/cursor.find/">.find()</a> sans <a href="http://docs.mongodb.org/manual/reference/method/cursor.sort/">.sort()</a>, it shows the obvious -- a full scan on the data, with no <code>IndexBounds</code> field.</em></p>
  
  <hr>
  
  <p>ADDENDUM: It's not one field, but all fields, that I'm trying to text index -- hence the <code>"$**"</code>.  As an experiment, I manually did <code>.ensureIndex(...)</code> on all fields, hoping this would help with the sort. But remember, I'm not trying to sort the text fields -- merely using that as a mechanism to get a collection of JSON objects that matched the search criteria. Once I have that collection, and I do get that collection, I'm trying to sort it by the <code>rank</code> field, which already does have an index and works in the other scenarios.</p>