group by slows down the query

Question

I have on the products table the following index: (product_t,productid,forsale). The MySQL manual says:

The GROUP BY names only columns that form a leftmost prefix of the index and no other columns. http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html

When I do the following query

SELECT z.product_t, COUNT(z.productid)
FROM xcart_products z
JOIN xcart_products_lng w ON z.productid = w.productid
AND w.code =  'US'
WHERE z.forsale =  'Y'
group by z.product_t

And therefore using the left most index field (product_t), the execution time is still massive:

+-----------+--------------------+
| product_t | COUNT(z.productid) |
+-----------+--------------------+
| B         |                  4 |
| C         |              10521 |
| D         |                  1 |
| F         |                 16 |
| G         |                363 |
| J         |                 16 |
| L         |                749 |
| M         |                 22 |
| O         |                279 |
| P         |               5304 |
| S         |                 22 |
| W         |                662 |
+-----------+--------------------+
12 rows in set (0.81 sec)

When I use the whole index (product_t,productid,forsale), the execution time is blazing fast (0.005 seconds). How should I change it to make the query go faster?

I think the query somehow could be improved through the use of a semi join... However i'm not sure how...

Answer 1

The slow down might not be related to the GROUP BY clause. Try adding an index for w.code and z.forsale individually.

MySQL Profiling might also help you in your endeavour