SQLite查询过慢求助：如何优化现有查询提升搜索速度

你的原查询存在两个核心问题，直接导致速度极慢：

• 用逗号拼接两个子查询会生成笛卡尔积，两个结果集的所有行两两匹配，数据量呈指数级增长
• 冗余的DISTINCT：FullConcordance若(BookID, Word, id)是唯一组合，DISTINCT完全多余；VersesPlain的id是主键，关联Books后不会产生重复行，DISTINCT只会增加计算开销

重构查询逻辑 #

从表结构和字段来看，你应该是要通过FullConcordance定位含指定单词的经文ID，再关联获取完整经文信息。正确写法用关联查询替代笛卡尔积：

SELECT
    c.id AS VERSEid,
    c.BookID,
    c.Word,
    b.Book,
    v.Chapter,
    v.Verse,
    v.VerseText
FROM FullConcordance c
JOIN VersesPlain v ON c.id = v.id
JOIN Books b ON v.BookID = b.id
WHERE c.BookID = ? 
  AND c.Word = ?

用?做参数占位符，避免SQL注入风险，同时提升查询稳定性。

索引优化（关键） #

缺少合适索引会导致SQLite全表扫描，创建以下索引直接提速：

1. 给FullConcordance创建复合索引，匹配查询条件和关联字段：

CREATE INDEX idx_fullconcordance_bookid_word_id ON FullConcordance(BookID, Word, id);

该索引让SQLite直接定位符合条件的行，同时直接获取关联所需的id，无需回表查询。

2. 给VersesPlain的BookID创建辅助索引（若经常按BookID过滤）：

CREATE INDEX idx_versesplain_bookid ON VersesPlain(BookID);

其他优化细节 #

• 移除不必要的DISTINCT：仅当关联后确实产生重复行时保留，否则浪费CPU资源
• 避免SELECT *：明确写出需要的字段，减少数据传输和内存占用
• 定期执行VACUUM命令：整理数据库文件碎片，提升读写效率

内容的提问来源于stack exchange，提问作者neowinston