StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMongoDB Optimizing FindAndModify or Find + Sort
text
Body
copied!<p>Running MongoDB, I am trying to queue a three column key lookup (a,b,c) with priority rank (d). I have a query like (pymongo syntax):</p> <pre><code>collection.find({'a':'foo','b':'bar','c':'baz'}, sort = [('d', -1)] ) </code></pre> <p>With a compound index (a+1,b+1,c+1,d-1) running an explain shows full table scan and a BasicCursor.</p> <p>With a single index on d, explain shows d index alone being used.</p> <p>For a large table, what I really want is to use the compound index. How can I make this work?</p> <p>INDEX_INFORMATION: {u'<em>id</em>': {u'key': [(u'_id', 1)], u'v': 1}, u'color_1_level_1_in_progress_1_Ranking_-1': {u'key': [(u'color', 1), (u'level', 1), (u'in_progress', 1), (u'Ranking', -1)], u'v': 1}}</p> <pre><code>EXPLAIN ON A FIND+SORT QUERY db.coll.find({'level' : {'$in' : [0,1,2]}, 'in_progress' : 0, 'color' : {'$in' : ['Red', 'Blue', 'Green]} }, sort = [('Ranking', -1)] ).explain() OUTPUT ON EXPLAIN OperationFailure: database error: too much data for sort() with no index. add an index or specify a smaller limit THE QUEUEING QUERY I CARE ABOUT OPTIMIZING coll.find_and_modify( query = {'level' : {'$in' : [0,1,2]}, 'in_progress' : 0, 'color' : {'$in' : ['Red', 'Blue', 'Green']} }, sort = {'Ranking' : -1}, update = {'$set': {'in_progress': 1}} ) </code></pre> <p>Note that when I add an index on the sort field 'Ranking', the explain returns that the Ranking index is being used. But the compound index is never used and performance is very slow on a 1.5 MM document test corpus.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload