我在一个mysql表1中有一组节点,在另一个表(表2)中有一个边表。节点带有主键,边缘使用此"外键"
**table1**
id label
1 node1
2 node2
3 node3
**table2**
FK_first FK_sec rel
1 3 guardian
2 1 guardian
1 3 times
我知道数据库的设计并不完美,但它很简单。。。现在,我想要每个节点的"rel"数量,并进行如下查询:
SELECT
label,
COUNT( rel ) as freq
FROM
`table1`
LEFT JOIN table2 ON (id=FK_first OR id=FK_second)
GROUP BY label
ORDER BY freq DESC
我有大约1000个节点和2000条边。如果查询为ON(id=FK_first或id=FK_second),则查询速度要快得多(<1秒)。另一个查询大约需要6秒,这太慢了。我将感谢一些评论,以加快这一点:-)
- LEFT JOIN table2 ON(id=FK_first或id=FK_second)~6秒
- LEFT JOIN table2 ON(id=FK_first)~0.16秒
LEFT JOIN table2 ON(id=FK_second)~0.16秒
id IN上的LEFT JOIN表2(FK_first,FK_second)~6秒
解释1:id select_type表类型possible_keys key key_len ref rows额外1 SIMPLE表1 ALL NULL NULL NULL NULL 2571使用临时;使用文件排序1 SIMPLE表2所有FK_first、FK_second、FK_first_2 NULL NULL 3858解释2:id select_type表类型possible_keys key key_len ref rows额外1 SIMPLE表1索引NULL PRIMARY 2 NULL 2571使用索引;使用临时;使用文件排序1 SIMPLE表2参考FK_first,FK_first_2 FK_first_2 4表1.id 1
尝试执行两个联接并将"OR"移动到COUNT()函数中:
对于每一行,它在FK1上连接表2一次,然后在FK2上再次连接(如果它还没有通过FK1连接到该行)。然后在COUNT中,我们指定只有连接的rel列为非null的行。
SELECT
label,
COUNT( table2A.rel || table2B.rel ) as freq
FROM
`table1`
LEFT JOIN
table2 as table2A
ON id=table2A.FK_first
LEFT JOIN
table2 as table2B
ON id=table2B.FK_second
AND table2A.FKFirst != table2B.FKFirst
GROUP BY label
ORDER BY freq DESC