加速SQLite查询

Question

我正在处理一个包含3个表的大型(~10 in ) SQLite数据库:包含id和"title“TEXT列的文档，包含id和"term”TEXT列的术语，以及包含id、"documentId“INT和"termId”INT的DocumentTerms，它们将从Terms表中的行映射到文档表中的行。如果文档多次包含同一术语，则在DocumentTerms表中有多个条目。

无论如何，我的问题是，我需要对每个术语运行一个tfidf术语权重，这涉及以下步骤(我包括了一个查询，我想我需要得到每个步骤)：

1. 查找该术语在所有文档中出现的次数- SELECT COUNT(*) FROM DocumentTerms WHERE termId=id
2. 找出总共有多少文档-- SELECT COUNT(*) FROM Documents
3. 查找一个术语出现在多少个文档中-- SELECT COUNT(*) FROM DocumentTerms WHERE termId=id GROUP BY documentId

显然，我只需要运行第二次。

有了这么大的数据库，每个查询都需要很长的时间。只要找出有多少个文档(SELECT COUNT(*) FROM Documents)，大约需要45秒。

为了加快速度，我尝试了一些实用的方法。我只需要运行这一次，所以我并不关心如果我在操作期间失去了电源或其他东西，DB是否会损坏：

PRAGMA synchronous = OFF
PRAGMA page_size = 4096
PRAGMA cache_size = 16384
PRAGMA temp_store = MEMORY
PRAGMA journal_mode = OFF
PRAGMA locking_mode = EXCLUSIVE

我怎样才能加快这些访问时间？

Answer 1

对于只读访问，大多数这些PRAGMA没有任何效果.只有cache_size是重要的；page_size可能有一点帮助，但必须在创建DB之前设置。

当一个简单的SELECT COUNT(*)花费那么长的时间时，很明显，您没有任何有用的索引。没有数据库结构很难判断，但以下索引可能会有所帮助：

CREATE INDEX Documents_Id ON Documents(Id)

(与Id不同，您可以使用Documents表的任何小字段，但通常使用主键索引是个好主意。)

CREATE INDEX DocumentTerms_termId_documentId ON DocumentTerms(termId, documentId)

您可以与解释查询计划检查查询是如何已执行的。