MySQL 自连接查询优化

Question

我有一个从单词列表生成的子字符串数据库。我正在执行比较以检索与某个输入词共享子字符串的所有词。

'word_substrings' 数据库格式和示例(对于单词 'aback' ):

    id (primary key), word_id (Foreign Key), word_substring (char(3))

    30                4                      "  a"
    31                4                      " ab"
    32                4                      "aba"
    33                4                      "bac"
    34                4                      "ack"
    35                4                      "ck "
    36                4                      "k  "

其中“word_id”是单词表中单词的关键字。

我试过等价的:

    select distinct t1.word_id 
        from word_substrings t1, word_substrings t2 
        where t1.word_substring = t2.word_substring 
        and t2.word_id = [some word_id]

以及表连接:

    select distinct t1.word_id
        from word_substrings as t1
        join word_substrings as t2 
        on t1.word_substring = t2.word_substring
        where and t2.word_id = [some word_id]

但是，这两个查询都需要大约 10 秒才能返回结果。

鉴于单词表和 word_substrings 表都容易发生变化，但数据会非常定期地检索，我尝试制作一个 View 来帮助缩短查询时间。但是，我发现返回时间没有名义上的变化。

我的单词列表目前有 40k 行，我的子字符串列表大约有 400k 行。

有没有人对如何优化查询或重新格式化数据库以缩短返回时间有任何想法？

我考虑过生成一个表，其中包含代表每个可能的子字符串的列，并在适当的列中注册每个单词，但我不太清楚这将如何工作。

感谢大家的帮助!如果有任何我遗漏的信息，我很乐意为您检索该数据。

注意:如果是相关信息，这是针对 Django 网络应用程序的。

Answer 1

您需要在 word_id 和 word_substring 上建立索引。 (此外，如果可以，请将列设置为 not null)

这样，仅使用 word_id 的查询将有效，其他使用 word_id 和 word_substring 的查询也将有效。

干杯。