使用ORDER BY子句时的Mysql慢联合查询

Question

我有两个表:附件和订单。

accessories.category包含类别列表，即红色、绿色、蓝色...orders表包含过去的订单列表。

我想选择：

10 records from [accessories.category] WHERE category = 'Red' AND
10 records from [accessories.category] WHERE category = 'Green' AND
10 records from [accessories.category] WHERE category = 'Blue'

因为我希望每个类别都有一定数量的记录，所以我决定使用UNION ALL查询，而且它确实可以工作。问题是它不够快，不能投入生产，因为在我的开发箱上执行它需要0.3秒。如果我去掉"ORDER BY sold DESC"，查询只需要0.0236秒，这是可以接受的。问题是我不能丢失ORDER BY。

顺便说一下:我已经为我需要的所有字段建立了索引。下面是查询：

(SELECT 
accessories.catno, 
accessories.category, 
accessories.header, 
accessories.description, 
accessories.specialoffer, 
accessories.picture, 
accessories.unit, 
accessories.addinfo, 
accessories.post, 
accessories.price, 
accessories.vat, 
accessories.soundclip, 
COUNT(orders.catno) AS sold 
FROM 
accessories, orders 
WHERE 
orders.catno = accessories.catno 
AND 
accessories.category = 'Red' 
AND accessories.stocklevel > 0 AND 
accessories.category = 'Red' 
GROUP BY catno 
ORDER BY sold DESC 
LIMIT 10) 

UNION ALL 

(SELECT 
accessories.catno, 
accessories.category, 
accessories.header, 
accessories.description, 
accessories.specialoffer, 
accessories.picture, 
accessories.unit, 
accessories.addinfo, 
accessories.post, 
accessories.price, 
accessories.vat, 
accessories.soundclip, 
COUNT(orders.catno) AS sold 
FROM 
accessories, orders 
WHERE 
orders.catno = accessories.catno 
AND 
accessories.category = 'Green' 
AND accessories.stocklevel > 0 AND 
accessories.category = 'Green' 
GROUP BY catno 
ORDER BY sold DESC 
LIMIT 10) 

UNION ALL

(SELECT 
accessories.catno, 
accessories.category, 
accessories.header, 
accessories.description, 
accessories.specialoffer, 
accessories.picture, 
accessories.unit, 
accessories.addinfo, 
accessories.post, 
accessories.price, 
accessories.vat, 
accessories.soundclip, 
COUNT(orders.catno) AS sold 
FROM 
accessories, orders 
WHERE 
orders.catno = accessories.catno 
AND 
accessories.category = 'Blue' 
AND accessories.stocklevel > 0 AND 
accessories.category = 'Blue' 
GROUP BY catno 
ORDER BY sold DESC 
LIMIT 10) 

我不确定我是否在这个查询中提出了太多的要求，或者我是否需要重新考虑。有什么想法吗？

添加了EXPLAIN的输出。

​

​

Answer 1

正如Alex建议的那样，您可以创建一个已售出的专栏，并使用触发器使其保持最新。或者你可以周期性地运行你的查询(每小时？每隔5分钟？每1分钟？)并将结果保存在另一个表中(或者更好的做法是，缓存我猜是从它生成的html，并完全避免查询)。

Answer 2

尝试此查询，尽管它未经过测试，但请尝试一下

SET @level = 0;
SET @group = '';

SELECT 
    *
FROM (
    SELECT
      accessories.catno,
      accessories.category,
      accessories.header,
      accessories.description,
      accessories.specialoffer,
      accessories.picture,
      accessories.unit,
      accessories.addinfo,
      accessories.post,
      accessories.price,
      accessories.vat,
      accessories.soundclip,
      COUNT(orders.catno)      AS sold,
          @level := IF(@group = accessories.category, @level+1, 1) AS level, 
          @group := accessories.category as E_Group 
    FROM accessories,
      orders
      left join orders
        on orders.catno = accessories.catno
    WHERE accessories.stocklevel > 0
    GROUP BY orders.catno, accessories.category
    ORDER BY orders.sold DESC
) rs
WHERE level < 11 

Answer 3

试一试这个查询

 (  SELECT * FROM ( SELECT
accessories.catno, 
accessories.category, 
accessories.header, 
accessories.description, 
accessories.specialoffer, 
accessories.picture, 
accessories.unit, 
accessories.addinfo, 
accessories.post, 
accessories.price, 
accessories.vat, 
accessories.soundclip, 
COUNT(orders.catno) AS sold 
FROM 
accessories, orders 
WHERE 
orders.catno = accessories.catno 
AND 
accessories.category = 'Red' 
AND accessories.stocklevel > 0 AND 
accessories.category = 'Red' 
GROUP BY catno )
ORDER BY sold DESC 
LIMIT 10 )

UNION ALL 

( SELECT * FROM (SELECT
accessories.catno, 
accessories.category, 
accessories.header, 
accessories.description, 
accessories.specialoffer, 
accessories.picture, 
accessories.unit, 
accessories.addinfo, 
accessories.post, 
accessories.price, 
accessories.vat, 
accessories.soundclip, 
COUNT(orders.catno) AS sold 
FROM 
accessories, orders 
WHERE 
orders.catno = accessories.catno 
AND 
accessories.category = 'Green' 
AND accessories.stocklevel > 0 AND 
accessories.category = 'Green' 
GROUP BY catno )
ORDER BY sold DESC 
LIMIT 10)

UNION ALL

( SELECT * FROM ( SELECT 
accessories.catno, 
accessories.category, 
accessories.header, 
accessories.description, 
accessories.specialoffer, 
accessories.picture, 
accessories.unit, 
accessories.addinfo, 
accessories.post, 
accessories.price, 
accessories.vat, 
accessories.soundclip, 
COUNT(orders.catno) AS sold 
FROM 
accessories, orders 
WHERE 
orders.catno = accessories.catno 
AND 
accessories.category = 'Blue' 
AND accessories.stocklevel > 0 AND 
accessories.category = 'Blue' 
GROUP BY catno )
ORDER BY sold DESC 
LIMIT 10)