Optimize SQL query when GROUP BY and ORDER BY expressions are different?
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
开发者_JAVA技巧
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
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
精彩评论