SQLite结合FTS与WHERE条件的关联查询优化求助

针对你遇到的组合查询缓慢但需要保留FTS rank字段的问题，以下是几个实用的优化思路：

1. 先筛选有效posts子集再关联FTS结果 #

优先用posts的索引过滤出符合root_id IN (12) AND deleted_at IS NULL的id集合，再和FTS查询结果做关联，大幅减少关联的数据量：

SELECT COUNT(p.id) AS count
FROM (
    SELECT id FROM posts 
    WHERE deleted_at IS NULL AND root_id IN (12)
) p
JOIN posts_fts fts ON p.id = fts.rowid
WHERE fts MATCH 'aqua* avai*'

这个写法会让SQLite先执行posts的快速索引扫描（利用已有的root_id和deleted_at索引），得到一个小的id列表后，再与FTS的查询结果做交集，避免全表级别的关联操作。

2. 调整JOIN顺序，优先执行FTS查询 #

把FTS表放在JOIN的左侧，让SQLite先执行快速的FTS匹配，再关联posts表做过滤，同时利用posts的索引快速验证条件：

SELECT COUNT(fts.rowid) AS count
FROM posts_fts fts
JOIN posts p ON fts.rowid = p.id
WHERE fts MATCH 'aqua* avai*'
  AND p.deleted_at IS NULL 
  AND p.root_id IN (12)
-- 业务需要rank时，直接加入字段和排序：
-- SELECT fts.rowid, fts.rank, p.title ... ORDER BY fts.rank DESC

FTS5的MATCH查询本身速度极快，先获取匹配的rowid集合后，再通过posts的索引验证root_id和deleted_at条件，整体效率会大幅提升。

3. 创建posts表的复合覆盖索引 #

现有单独的root_id和deleted_at索引虽然能过滤数据，但查询时可能需要回表取id。创建包含过滤条件和id的复合覆盖索引，让过滤查询直接从索引返回结果，不需要访问表数据：

CREATE INDEX idx_posts_root_deleted_id ON posts (root_id, deleted_at, id);

这个索引覆盖了所有查询需要的字段，执行SELECT id FROM posts WHERE root_id IN (12) AND deleted_at IS NULL时，完全不需要读取posts表的主数据，进一步缩短查询时间。

4. 用CTE明确执行逻辑引导优化器 #

使用WITH子句分别定义有效posts集合和FTS结果集合，让SQLite优化器更清晰地识别出先计算两个小数据集再关联的最优路径：

WITH valid_posts AS (
    SELECT id FROM posts 
    WHERE deleted_at IS NULL AND root_id IN (12)
), fts_matches AS (
    SELECT rowid AS id, rank FROM posts_fts 
    WHERE posts_fts MATCH 'aqua* avai*'
)
SELECT COUNT(v.id) AS count
FROM valid_posts v
JOIN fts_matches f ON v.id = f.id
-- 需要排序时：ORDER BY f.rank

CTE的写法避免了优化器选择错误的执行顺序（比如先全表JOIN再过滤），确保两个子集都快速计算完成后再做交集。

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