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: