spree/spree — pull request #1163 by gfmurphy (2012-02-16 19:59:39+00:00)
Title: Use correlated subquery in Product.on_hand scope to increase performance

We're deploying a store on 1.0 with 15K product variants. The current version of the *Product.on_hand* scope wasn't performing well in MySQL with a "large" table.

This change uses a correlated subquery improved query performance for us by more than 2 orders of magnitude. It was the best performing option we tried on MySQL.

Current specs for product scopes are passing with this change, but it's not clear how other databases will optimize new query.

We would welcome any feedback on getting something similar into master and 1-0-stable