MYSQL order by + group by...最快的方法？

Question

我正在尝试优化 SQL 查询，我想了解一些专家关于组合 GROUP BY 和 ORDER BY 的最佳/最快方法的意见

基本上，我试图从产品表中选择最低价格，并按商家名称对它们进行分组。

这是我原来的查询:

从数据源中选择 p.*、m.*、d.* 作为 d，产品作为 p 左外连接元作为 p.mykey = m.mykey 上的 m，其中 p.datafeed_id = d.id 和 ( match(p.name) against ('+asics +"gel"-women*' in boolean mode)) and p.datafeed_id = '35' and p.is_custom = 0 group by d.merchant_name order by d.merchant_name limit 50

ORDER BY 不起作用，我得到的是分组产品，但不是价格最低的产品。

阅读其他讨论后，我提出了一个改进的查询:

SELECT p . * , m . * , d . * 
FROM datafeeds AS d, products AS p
INNER JOIN (

SELECT MIN( display_price ) AS MinPrice
FROM products AS p
WHERE 1 =1
AND (

MATCH (
p.name
)
AGAINST (
'+asics +"gel" -women*'
IN BOOLEAN
MODE
)
)
AND p.datafeed_id =  '35'
AND p.is_custom =0
GROUP BY merchant_name
) AS p2 ON p.display_price = p2.MinPrice
LEFT OUTER JOIN meta AS m ON p.mykey = m.mykey
WHERE p.datafeed_id = d.id
AND (

MATCH (
p.name
)
AGAINST (
'+asics +"gel" -women*'
IN BOOLEAN
MODE
)
)
AND p.datafeed_id =  '35'
AND p.is_custom =0
GROUP BY d.merchant_name
ORDER BY d.merchant_name
LIMIT 50`

查询得到了正确的结果，但速度相当慢。有更好的方法吗？

提前致谢

Answer 1

你可以试试这个

SELECT          p.*, 
                m.*, 
                d.* 
FROM            datafeeds AS d, 
                ( 
                         SELECT   * 
                         FROM     products 
                         WHERE    1 = 1 
                         AND      ( 
                                           MATCH ( name ) against ( '+asics +"gel" -women*' IN boolean mode ) ) 
                         AND      datafeed_id = '35' 
                         AND      is_custom =0 
                         ORDER BY merchant_name, 
                                  display_price) AS p 
LEFT OUTER JOIN meta                             AS m 
ON              p.mykey = m.mykey 
WHERE           p.datafeed_id = d.id 
GROUP BY        d.merchant_name 
ORDER BY        d.merchant_name 
LIMIT           50