SQLITE FTS3 查询速度比标准 Tabel 慢

Question

>我从源代码构建了 sqlite3 以包含 FTS3 支持，然后在包含 150 万行数据的现有 sqlite 数据库中创建了一个新表，使用

CREATE VIRTUAL TABLE data USING FTS3(codes text);

然后使用

INSERT INTO data(codes) SELECT originalcodes FROM original_data;

然后查询每个表

SELECT * FROM original_data WHERE originalcodes='RH12';

这立即返回，因为我在该列上有一个索引

对 FTS3 表的查询

SELECT * FROM data WHERE codes='RH12';

需要近 28 秒

有人可以帮助解释我做错了什么，因为我希望这会更快

Answer 1

文档解释：

可以使用两种不同形式的 SELECT 语句有效地查询 FTS 表：

• 按行查询。如果 SELECT 语句的 WHERE 子句包含形式为 "rowid = ？" 的子句，则 ？是一个SQL表达式，FTS能够使用相当于SQLite整数主键索引直接检索请求的行。
• 全文查询。如果 SELECT 语句的 WHERE 子句包含形式为"MATCH？"的子句，则 FTS 能够使用内置的全文索引将搜索限制为与指定为 MATCH 子句的右操作数的全文查询字符串匹配的文档。

如果这两种查询策略都不能使用，则使用整个表的线性扫描来实现对 FTS 表的所有查询。

为了进行有效的查询，您应该使用

SELECT * FROM data WHERE codes MATCH 'RH12'

但这将找到包含搜索字符串的所有记录。

为了有效地执行"正常"查询，您必须在普通表中保留数据的副本。（如果要节省空间，可以使用无内容或外部内容表。

Answer 2

您应该更仔细地阅读文档。

使用

WHERE col = 'value' 对虚拟 FTS 表的任何查询都会很慢（针对ROWID的查询除外），但使用 WHERE col MATCH 'value' 的查询将使用 FTS 并且速度很快。

Answer 3

我不是这方面的专家，但这里有几件事需要考虑。你的测试是有缺陷的（我认为）。您正在对比具有精确文本匹配（索引可用于original_data - 没有什么会胜过这种情况）与 fts3 表上的相等（我不确定 FTS3 是否会在这种类型的查询中发挥作用）。如果你想比较苹果与苹果（看看FTS3的好处），你将需要比较original_data上的"喜欢"操作与FTS3对数据的"匹配"操作。