SQLite "LIKE"运算符与 "="运算符相比非常慢

Question

当我在 SQLite 中使用 LIKE 运算符时，与使用 = 相比，速度非常慢。使用 = 运算符大约需要 14ms，但是当我使用 LIKE 时，大约需要 440ms。我正在使用 DB Browser for SQLite 对此进行测试。这是运行速度很快的查询:

SELECT re.ENTRY_ID, 
       GROUP_CONCAT(re.READING_ELEMENT, '§') AS read_element,
       GROUP_CONCAT(re.FURIGANA_BOTTOM, '§') AS furigana_bottom,
       GROUP_CONCAT(re.FURIGANA_TOP, '§') AS furigana_top,
       GROUP_CONCAT(re.NO_KANJI, '§') AS no_kanji,
       GROUP_CONCAT(re.READING_COMMONNESS, '§') AS read_commonness, 
       GROUP_CONCAT(re.READING_RELATION, '§') AS read_rel,
       GROUP_CONCAT(se.SENSE_ID, '§') AS sense_id, 
       GROUP_CONCAT(se.GLOSS, '§') AS gloss, 
       GROUP_CONCAT(se.POS, '§') AS pos, 
       GROUP_CONCAT(se.FIELD, '§') AS field,
       GROUP_CONCAT(se.DIALECT, '§') AS dialect, 
       GROUP_CONCAT(se.INFORMATION, '§') AS info 
FROM Jmdict_Reading_Element AS re LEFT JOIN 
     Jmdict_Sense_Element AS
     se ON re.ENTRY_ID = se.ENTRY_ID
WHERE re.ENTRY_ID IN (SELECT ENTRY_ID FROM Jmdict_Reading_Element WHERE READING_ELEMENT = 'example') OR 
      re.ENTRY_ID IN (SELECT ENTRY_ID FROM Jmdict_Sense_Element WHERE GLOSS = 'example')
 GROUP BY re.ENTRY_ID

当我改变时速度会变慢

WHERE re.ENTRY_ID IN (SELECT ENTRY_ID FROM Jmdict_Reading_Element WHERE READING_ELEMENT = 'example') OR 
re.ENTRY_ID IN (SELECT ENTRY_ID FROM Jmdict_Sense_Element WHERE GLOSS = 'example')

至

WHERE re.ENTRY_ID IN (SELECT ENTRY_ID FROM Jmdict_Reading_Element WHERE READING_ELEMENT LIKE 'example') OR 
re.ENTRY_ID IN (SELECT ENTRY_ID FROM Jmdict_Sense_Element WHERE GLOSS LIKE 'example')

我需要这样做，以便我可以使用通配符，例如

WHERE re.ENTRY_ID IN (SELECT ENTRY_ID FROM Jmdict_Reading_Element WHERE READING_ELEMENT LIKE 'example%') OR 
re.ENTRY_ID IN (SELECT ENTRY_ID FROM Jmdict_Sense_Element WHERE GLOSS LIKE 'example%')

这是数据库本身的链接: https://www.mediafire.com/file/hyuymc84022gzq7/dictionary.db/file

谢谢

Answer 1

尝试在您正在使用的列上放置全文索引。

参见Full Text Indexing

创建目录

USE {yourDB}  
GO  
CREATE FULLTEXT CATALOG {catalogName}
WITH ACCENT_SENSITIVITY = OFF

创建索引

USE {yourDB}  
GO  
CREATE FULLTEXT INDEX ON {someTable} ({col1}, {col2})
ON catalogName

注意这更方便，但请查看您的排序规则是否不区分大小写，例如“a”=“A”。通常，排序规则将具有 ci_utf8 例如(ci = 不区分大小写)。我这样做是为了用户和程序员的方便。