Optimize SQL query when GROUP BY and ORDER BY expressions are different?

Question

Hello all

From the Order By Optimization in Mysql documentation, I quote...

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

• You have different ORDER BY and GROUP BY expressions.

Is there any darn workaround for this?

My query is...

SELECT *
FROM (`product`)
WHERE `Category1` = 'PC'
AND `Category2` = 'desktop'
GROUP BY `product_code`
ORDER BY `reviews` desc, `popularity` desc
LIMIT 10

Explain output...

select_type: SIMPLE
table: product
type: ref
possible_keys: Category1_idx
key: Category1_idx
key_len: 390
ref: const, const
rows: 508
Extra: Using where; Using temporary

Regards

Answer 1

GROUP BY is normally used with functions like SUM() to aggregate records. Your query doesn't seem to require the group by clause as such. Would the following work better?

SELECT * FROM (product) WHERE Category1 = 'PC' AND Category2 = 'desktop' ORDER BY product_code, reviews desc, popularity desc LIMIT 10

You would create an index to match of course.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html