So we're experimenting with dropping MongoDB into production here with some simple stuff. We ran into a problem where simple selects (that have indexes!) were taking 300msec to return. After digging into the database profiler (which is built-in and quite well done), we noticed something odd in the query -- it was full table scanning 600K rows. Why? Brett figured that it was this term in the query: orderby: { $natural: 1 }
But where had that come from? We didn't add it. Turns out MongoMapper uses it by default when you call find(:first, ...). Unfortunately this order by can overrule any indexes you might have intended on using. We fixed this by adding intentional ordering to our find query that matched our indexes: find(:first, :conditions => {:model_id => model.id, :model_class => model.class.to_s}, :order => 'model_id asc, model_class asc') 300msec queries suddenly jumped down to 4 milliseconds. Problem solved. But very non-intuitive.
But where had that come from? We didn't add it. Turns out MongoMapper uses it by default when you call find(:first, ...). Unfortunately this order by can overrule any indexes you might have intended on using. We fixed this by adding intentional ordering to our find query that matched our indexes: find(:first, :conditions => {:model_id => model.id, :model_class => model.class.to_s}, :order => 'model_id asc, model_class asc') 300msec queries suddenly jumped down to 4 milliseconds. Problem solved. But very non-intuitive.