我有以下查询:
SELECT *
FROM products
INNER JOIN product_meta
ON products.id = product_meta.product_id
JOIN sales_rights
ON product_meta.product_id = sales_rights.product_id
WHERE ( products.categories REGEXP '[[:<:]]5[[:>:]]' )
AND ( active = '1' )
AND ( products.show_browse = 1 )
AND ( product_meta.software_platform_mac IS NOT NULL )
AND ( sales_rights.country_id = '240'
OR sales_rights.country_id = '223' )
GROUP BY products.id
ORDER BY products.avg_rating DESC
LIMIT 0, 18;
省略ORDER BY
部分运行查询,查询时间约为90毫秒;加上ORDER BY
部分后,查询需要约8秒。
我在SO上浏览了一下,发现原因可能是在返回所有数据之前就执行了排序,而我们应该在结果集上运行ORDER BY
?(参见此帖子:使用ORDER BY时查询缓慢)
但我还无法确定如何明确地执行此操作?
SELECT *
,修复应该很明显。如果失败了,请编辑您的问题并发布该查询中所有表的SQL DDL。 - Mike Sherrill 'Cat Recall'