这个问题对我来说有点复杂,我不能用一句话来解释,所以标题可能看起来很模糊。
我的MySQL数据库中有3个表,它们的结构如下:
单词表(500万行)
+-----+--------+
|寡言|
+-----+--------+
|一| foo|
|2 |巴|
|3 |你好|
+-----+--------+
纸字关系(1000万行)
+-----+-------+
|pid |字|
+-----+-------+
|1 | 1 |
|1 | 2 |
|1 | 3 |
|2 | 1 |
|2 | 3 |
+-----+-------+
论文引文关系(80K行)
+----------+--------+
|pid|从| pid|到|
+----------+--------+
|1 | 2 |
|1 | 3 |
|1 | 4个|
|2 | 1个|
|2 | 3个|
+----------+--------+
我想知道有多少篇论文包含W这个词,并引用这些论文中也包含W这个词(对于列表中的每个词)
我使用两个内部连接来完成这项工作,但是当这个词流行时(超过50秒(如果这个词很少使用的话,速度会很快,低于0.1秒),这是我的代码
从中选择计数(*)(
选择a.pid_from,a.pid_to,b.word from paper_引文关系作为
在a.pid_from=b.pid上,内部连接纸_word_关系为b
在a.pid_to=c.pid上,内连接纸_word_关系为c
其中b.word=2和c.word=2)作为d
我怎么能做得更快?我的查询是不够高效还是数据量的问题?
我只能想出一个解决方案,删除paper_word_relation
表中出现少于2个的单词。(约400万字只出现一次)
谢谢!
最佳答案
如果只关心获取计数,则不应首先将结果获取到派生表中,然后再将行计数出来。这可能会创建不必要的临时表,将大量数据存储在内存中。您可以直接计算行数。
我也认为你需要计算独特的论文数量。由于paper_citation_relation
表中存在多对多关系,一张纸可能会出现重复行。
SELECT COUNT(DISTINCT a.pid_from)
FROM paper_citation_relation AS a
INNER JOIN paper_word_relation AS b ON a.pid_from = b.pid
INNER JOIN paper_word_relation AS c ON a.pid_to = c.pid
WHERE b.word = 2 AND c.word = 2
对于性能,您需要下列索引:
(pid_from, pid_to)
表中paper_citation_relation
上的综合指数。(pid, word)
表中paper_word_relation
上的综合指数。我们还可以通过减少一个连接并在
AND/OR
中使用基于条件的过滤来进一步优化查询。不过,你需要对它进行基准测试。SELECT COUNT(*)
FROM (
SELECT a.pid_from
FROM paper_citation_relation AS a
INNER JOIN paper_word_relation AS b
ON (a.pid_from = b.pid OR
a.pid_to = b.pid)
GROUP BY a.pid_from
HAVING SUM(a.pid_from = b.pid AND b.word = 2) AND
SUM(a.pid_to = b.pid AND b.word = 2)
)