SQL嵌套查询使用IN慢 [英] SQL Nested Query slow using IN

问题描述

我在MySQL中有WordNet词法数据库。我期待找到给定单词的同义词。目前，数据以三对多关系表示：

字 - （147,000行）

wordid，word

synsets - （119,000行）

synsetid

sense - （206,000行）

wordid，synsetid

所有表都设置了索引。

每个单词可以有多个同义词，每个同义词可以有多个单词。我正在寻找返回给定单词的所有同义词的所有单词。每个单词往往会有大约2个同义词（一个用于动词用法，一个用于名词）。我为此使用的SQL查询是：

  SELECT w.word 
 FROM sense s 
 INNER JOIN words w 
 ON s.wordid = w.wordid 
 WHERE s.synsetid 
 IN 
（
 SELECT s.synsetid 
 FROM words w 
 INNER JOIN sense s 
 ON w.wordid = s.wordid 
 WHERE w.word = word_to_search
）
 AND w.word<> 'word_to_search'ORDER BY synsetid 
  

然而这似乎需要很长时间（〜0.75秒） 。当你将查询分开时，内部查询需要〜0.0005秒，而每个外部查询需要类似。

那么我做错了什么？是否有更合适的方式来构造这个查询？

编辑：

所以我在阅读下面链接的文章后提出的解决方案是：

  SELECT w.word 
 FROM sense s 
 INNER JOIN words w 
 ON s.wordid = w.wordid 
 JOIN 
（
 SELECT s.synsetid 
 FROM words w 
 INNER JOIN sense s 
 ON w.wordid = s.wordid 
 WHERE w.word =word_to_search
）i 
 ON i.synsetid = s.synsetid 
  

执行时间大约为0.0008秒。

解决方案

避免IN和NOT IN （在你的情况下只是在IN） INNER JOIN ON 可以提升性能。

编辑：

这些链接： link 1

link 2

研究JOINS与IN的有效性以及其他可互换的操作。然而，他们认为IN和NOT IN 不需要避免。

I have the WordNet lexical database in MySQL. I am looking to find synonyms of given words. Currently the data is set out in three tables as a many-to-many relationship:

words - (147,000 rows)
wordid, word

synsets - (119,000 rows)
synsetid

sense - (206,000 rows)
wordid, synsetid

All tables have indexes set up on them.

Each word can have several synsets and each synset can have several words. I am looking to return all words for all synsets for a given word. There tends to be around 2 synsets for each word (one for the verb usage, one for the noun) The SQL query I'm using for this is:

SELECT w.word
FROM sense s
INNER JOIN words w
ON s.wordid = w.wordid
WHERE s.synsetid 
IN
(
SELECT s.synsetid
FROM words w
INNER JOIN sense s
ON w.wordid = s.wordid 
WHERE w.word = "word_to_search"
)
AND w.word <> 'word_to_search' ORDER BY synsetid

This seems to be taking a very long time however (~0.75 secs). When you split the queries up they take ~0.0005 secs for the inner query and similar for each of the outer queries.

So what am I doing wrong? Is there a much more appropriate way to structure this query?

EDIT:

So the solution I have come up with after reading the linked articles below is:

SELECT w.word
FROM sense s
INNER JOIN words w
ON s.wordid = w.wordid
JOIN
(
SELECT s.synsetid
FROM words w
INNER JOIN sense s
ON w.wordid = s.wordid 
WHERE w.word = "word_to_search"
) i
ON i.synsetid = s.synsetid

This executes in ~0.0008 sec.

解决方案

avoiding IN and NOT IN (in your case just IN) with INNER JOIN ON could boost performance.

edit:

These links:

link 1

link 2

research the effectiveness of JOINS vs IN's and other interchangeable operations. They, however, conclude that IN and NOT IN does not need to be avoided.

这篇关于SQL嵌套查询使用IN慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！