ihavethewordnetlexicaldatabaseinmysql.iamlookingtofindsynonymsofgivenwords.currentlythedataissetoutinthreetablesasamany--
words-- (147，000r ows )。
wordid，word
同步设置--(119，000r ows )。
同步id
sense-- (206，000r ows )。
wordid，synsetid
alltableshaveindexessetuponthem。
eachwordcanhaveseveralsynsetsandeachsynsetcanhaveseveralwords.iamlookingtoreturnallwordsforallsynsetsforagivenword.therereteteted chword(onefortheverbusage，one for the noun ) thesqlqueryi ' musingforthisis 3360
SELECT w.word
来自传感器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 '
）
andw.word ' word _ to _ search ' orderbysynsetid
thisseemstobetakingaverylongtimehowever (0.75 secs ).whenyousplitthequeriesuptheytake ) 0.0005 secsfortheinnnnerqueryandsimilillloke
So what am I doing wrong？ isthereamuchmoreappropriatewaytostructurethisquery？
EDIT:
sothesolutionihavecomeupwithafterreadingthelinkedarticlesbelowis :
SELECT w.word
来自传感器s
INNER JOIN words w
ON s.wordid=w.wordid
约翰
（
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。
解决方案
Avoidinginandnotin (Inyourcasejustin ) withinnerjoinoncouldboostperformance。
edit:
These links:
researchtheeffectivenessofjoinsvsin ' sandotherinterchangeableoperations.they，however，concludethatinandnotindoesnoesnotneeeeeeeeedededededed