MongoDB: Optimize index to avoid the scanAndOrder operation

Recently,I’ve got a performance issue with MongoDB index,it confused me and took me awhile to figure it out.let me try to get this issue straight:I have a compound index on multiple fields,but always result in a slow scan and order operation when performing a range query ,which means that the index is not being used for sorting.

For example,if I have an index “{a:1, b:1, c:-1}”,it works well for find({a:1,b:2}).sort({c:-1}), but  very slowly for find( {a:{$in:[1,2,3]}, b:4}).sort({c:-1}),here’s the output of the explain plan:

{
	"Cursor:BtreeCursor" : a_1_b_1_c_-1,
	"nscanned" : 13567
       	...
	"scanAndOrder" : true,
	...
}

There are many documents said that the sort column should be the last column used in the index,but it’s not always true,especailly if you are doing a range query, such as $lt,$in and etc.it  does complicate the query processing and make it hard to use the index to satisfy the sort conditions.indeed,in this case,you should put the sort field before range query field in your index,for example, “{c:-1,a:1,b:1}”  in this case.

Leave a Reply

Your email address will not be published.