mysql - 如何使用 Join 在 MySql 中应用索引

Question

我对索引不是很熟悉。我正在记录上应用索引，因为在表上查找结果需要花费很多时间。查询工作正常

SELECT words.wordid, synsets.synsetid, pos, definition
FROM words, senses, synsets
WHERE words.wordid = senses.wordid
AND senses.synsetid = synsets.synsetid
AND lemma='apple'


| wordid | lemma               |                       
+--------+--------------------+-
|  1     | Apply               |
|  2     | PRIMARY             |
|  3     | DEPENDENT           |
+----+--------------------+-----

其他表格

<小时/>

wordid  casedwordid  synsetid  senseid  sensenum    laxid   tagcount
1         19        108659519   1       1       0   0
2       1-hitter    108970180   2       1       0   0
3        NULL       104510146   5       1       0   0 

第三个同义词表

感官表

<小时/>

synsetid   pos     lexdomainid  definition
108659519   n       3   that which is perceived or known or inferred to have its
108970180   n       3   an entity that has physical existance   
104510146   n       3   a general concept formed by extracting common features   

sql 查询工作正常，但现在我必须应用索引来获得比简单查询更快的结果。

如何在这三个表上应用索引

Answer 1

如果你只像这样运行 SELECT 则

SELECT words.wordid, synsets.synsetid, pos, definition
    FROM words, senses, synsets
    WHERE words.wordid = senses.wordid
    AND senses.synsetid = synsets.synsetid
    AND lemma='apple'

您需要这些索引(请参阅手册中的更多信息:http://dev.mysql.com/doc/refman/5.7/en/create-index.html):

-- create simple b tree indexes for int fields (improve join performance)
CREATE INDEX words_wordid ON words (wordid);
CREATE INDEX senses_wordid ON senses (wordid);
CREATE INDEX senses_synsetid ON senses (synsetid);
CREATE INDEX synsets_synsetid ON synsets (synsetid);

-- we create an index using the first 20 characters of the lemma
CREATE INDEX words_lemma ON synsets (lemma(20));

如果您想运行另一个选择查询，那么您应该添加更多索引以提高选择性能:)