select - sqlite3 数据库有助于提高性能和设计

Question

我有一个具有此架构的 sqlite3 数据库:

CREATE TABLE [dict] (
  [Entry] [CHAR(209)], 
  [Definition] [CHAR(924975)]);

CREATE INDEX [i_dict_entry] ON [dict] ([Entry]);

它是一种有 260000 条记录和近 1GB 大小的字典；我为 Entry 创建了一个索引提高性能的列；

行的条目列示例如下:

|love|lovingly|loves|loved|loving|

所有用 | 分隔的单词指的是相同的定义；(我将它们全部放在一个字符串中，用 | 分隔以防止 Definition 列中的数据重复)

这是我用来检索结果的命令:

SELECT * FROM dict WHERE Entry like '%|loves|%'

执行时间:~1.7s

如果我使用 =运算符而不是 LIKE运算符，执行几乎是瞬时的；

SELECT * FROM dict WHERE Entry='|love|lovingly|loves|loved|loving|'

但这样我就不能搜索像这样的词:爱，爱......(我的意思是分开)

我的问题:

虽然我已经为 Entry 创建了一个索引列，当我们使用 LIKE 时，索引真的有效吗？带有 % 的运算符在里面？

我为复合的每个部分创建不同的行的想法怎么样 Entry列(一个用于爱另一个用于爱...然后所有的定义都相同)，然后使用 =运算符(operator)？如是;反正有数据的引用吗？我的意思是而不是重复相同的 Definition对于每个条目，创建一个并且所有其他条目都指向它；是否可以？

提前感谢您的任何提示和建议；

Answer 1

每个条目都应该在数据库中有一个单独的行:

CREATE TABLE Definitions (
    DefinitionID INTEGER PRIMARY KEY,
    Definition TEXT
);
CREATE TABLE Entries (
    EntryID INTEGER PRIMARY KEY,
    DefinitionID INTEGER REFERENCES Definitions(DefinitionID),
    Entry TEXT
);
CREATE INDEX i_entry ON Entries(Entry);

然后，您可以通过连接两个表来查询定义:

SELECT Definition
FROM Entries
JOIN Definitions USING (DefinitionID)
WHERE Entry = 'loves'

另见 Database normalization .