这是我问题的SQL小提琴:http://sqlfiddle.com/#!9/8ad87/2
我有表格:组,优惠和offers_groups。
组具有名称,但这些名称分配有特殊的 ID。Id 不是自动递增的,它们是以这种方式预定义的。
这是组表的一小部分:
id name
0300000 Agro
0310000 Fruits
0311000 Peach
0320000 Vegetables
在报价表中存储了人员和公司提供的报价:
id offer
1 Selling fruits
2 Selling peanuts
3 Selling Vegetables
4 Selling peach
5 We do Agro
6 We sell Peach
7 We sell Vegetables
8 We transport Vegetables
9 We transport Peaches
offers_groups表负责连接这两者:
id offer_id group_id
1 1 3100000
2 2 0300000
3 3 0311000
4 4 0300000
5 5 0311000
6 6 0320000
7 7 0320000
8 3 0311000
我需要计算哪些组的报价最多,并将这些组显示给用户。
这是我的查询:
SELECT og.id, og.offer_id, og.group_id,
o.id, o.offer,
g.id, g.name,
COUNT(og.offer_id) AS offersCount
FROM `offers_groups` og
JOIN `offers` o ON og.offer_id = o.id
JOIN `groups` g ON og.group_id = g.id
GROUP BY og.group_id
ORDER BY offersCount DESC
LIMIT 2
我的 SQL Fiddle 中的代码正在工作,我认为它给了我正确的结果,但是在这些表包含大量数据的真实数据库中,此查询正在执行 25 秒。而且我不相信这是因为列索引,对吧?我希望一些专家可以告诉我我的查询是否设计得不好。我可以做一些事情来加快速度,同时获得正确的结果吗?
谢谢
首先,select
中的所有列都没有意义。 它们应该只在"组"级别,而不是在"产品/服务组"级别。 因此,让我专注于此查询:
SELECT g.id, g.name,
COUNT(og.offer_id) AS offersCount
FROM `offers_groups` og JOIN
`offers` o
ON og.offer_id = o.id JOIN
`groups` g
ON og.group_id = g.id
GROUP BY g.id
ORDER BY offersCount DESC
LIMIT 2;
其次,在任何结构合理的数据库中,您实际上并不需要offers
表——外键关系应确保行实际存在。 所以,试试这个:
SELECT g.id, g.name,
COUNT(og.offer_id) AS offersCount
FROM `offers_groups` og JOIN
`groups` g
ON og.group_id = g.id
GROUP BY g.id
ORDER BY offersCount DESC
LIMIT 2;
然后,根据您的 SQL Fiddle,您的表上没有索引。 将所有id
定义为主键。 例如:
CREATE TABLE IF NOT EXISTS `offers` (
`id` int(11) NOT NULL PRIMARY KEY,
`offer` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;
使用主键应该可以解决性能问题。