wp-e-commerce/WP-e-Commerce — pull request #755 by leewillis77 (2013-12-26 20:36:18+00:00)
Title: Wpsc product on special potential optimisations

As per the previous comments on #753 this PR avoids sorting the query results (Since we only care how many results there are not which they are, or what order they are in), and only returns a count, not the actual variation prices since that's all the code uses.

Before this change, the SQL characteristics are:
```
+----+-------------+-------+------+--------------------------------------+-------------+---------+--------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------------------------+-------------+---------+--------------------+------+----------------------------------------------+
| 1 | SIMPLE | p | ref | PRIMARY,type_status_date,post_parent | post_parent | 8 | const | 2 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | pm2 | ref | post_id,meta_key | post_id | 8 | wpec38.p.ID | 4 | Using where |
| 1 | SIMPLE | pm3 | ref | post_id,meta_key | post_id | 8 | wpec38.p.ID | 4 | Using where |
| 1 | SIMPLE | pm | ref | post_id | post_id | 8 | wpec38.pm2.post_id | 4 | Using where |
+----+-------------+-------+------+--------------------------------------+-------------+---------+--------------------+------+----------------------------------------------+
```

After this change, they become:
```
+----+-------------+-------+------+--------------------------------------+-------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------------------------+-------------+---------+-------------------+------+-------------+
| 1 | SIMPLE | p | ref | PRIMARY,type_status_date,post_parent | post_parent | 8 | const | 2 | Using where |
| 1 | SIMPLE | pm | ref | post_id,meta_key | post_id | 8 | wpec38.p.ID | 4 | Using where |
| 1 | SIMPLE | pm2 | ref | post_id,meta_key | post_id | 8 | wpec38.p.ID | 4 | Using where |
| 1 | SIMPLE | pm3 | ref | post_id,meta_key | post_id | 8 | wpec38.pm.post_id | 4 | Using where |
+----+-------------+-------+------+--------------------------------------+-------------+---------+-------------------+------+-------------+
```

This is on a test site with a pretty small number of variations, and even fewer on sale :) The main change between the two queries is that we've lost the "using temporary" and "using filesort" which should help on larger stores. There's also less data being passed between MySQL and PHP although I can't see that being a big performance win. Feedback appreciated from people with larger stores / more complex variations.