MYSQL按+组按.最快的方法？

Question

我正在尝试优化一个SQL查询，我希望对组合组BY和ORDER的最佳/最快方法提供一些专家意见。

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

这是我最初的疑问：

select p.*, m.*, d.* from datafeeds as d, products as p 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

定单不起作用，我得到的是成批的产品，而不是价格最低的产品。

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

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