SQLite跨两表搜索优化：FTS5尝试无结果，求非FTS5方案

首先明确核心问题：你的查询里用了%COOP%这种前后通配的模糊匹配，SQLite的普通B树索引对这种模式完全无效——因为索引是按字符顺序排序的，没法定位到中间包含关键词的记录，所以clients表的索引根本用不上。结合你的场景，给你几个可行的优化方向：

1. 把LEFT JOIN改成INNER JOIN #

你的WHERE子句里引用了clients表的字段（比如cl.businessName LIKE '%COOP%'），如果某个order没有对应的client，这些条件必然不成立，所以LEFT JOIN和INNER JOIN效果完全一样，但INNER JOIN能提前过滤掉无匹配的orders，减少后续需要处理的数据量。

2. 拆分查询范围，先缩小匹配集合 #

不要直接在JOIN后的大结果集上做模糊搜索，先分别找出符合条件的clients和orders，再关联查询：

WITH matching_clients AS (
    SELECT id FROM clients 
    WHERE businessName LIKE '%COOP%' 
       OR companyName LIKE '%COOP%' 
       OR name LIKE '%COOP%' 
       OR businessNumber LIKE '%COOP%' 
       OR email LIKE '%COOP%'
),
matching_orders AS (
    SELECT id FROM orders 
    WHERE isRecurringOrder = 0 
      AND note LIKE '%COOP%'
)
SELECT
  o.id, o.placedTime, cl.businessName, cl.companyName, cl.name, cl.businessNumber, o.note, cl.email
FROM orders o
JOIN clients cl ON o.clientId = cl.id
WHERE 
  o.isRecurringOrder = 0 
  AND (o.id IN (SELECT id FROM matching_orders) OR cl.id IN (SELECT id FROM matching_clients))
ORDER BY o.placedTime DESC;

这种方式先通过两个子查询分别过滤出符合条件的client id和order id，再关联获取详情，比直接在全表JOIN后过滤要高效得多。

3. 建立针对性的联合索引 #

给orders表创建(isRecurringOrder, placedTime DESC)的联合索引：

CREATE INDEX idx_orders_recurring_placed ON orders(isRecurringOrder, placedTime DESC);

这个索引能让数据库快速过滤出isRecurringOrder = 0的订单，并且直接按placedTime降序的顺序读取数据，避免额外的排序操作（你的原查询里的ORDER BY会触发排序，有了这个索引可以跳过排序步骤）。

4. 其他辅助优化 #

• 如果业务允许，限制查询的时间范围（比如只查最近3个月的订单），进一步减少数据量；
• 定期归档历史订单到单独的表，保持主orders表的数据量可控；
• 可以尝试用INSTR(cl.businessName, 'COOP') > 0替代LIKE '%COOP%'，两者逻辑等价，部分场景下SQLite的优化器处理效率会略高。

另外提一句：你之前创建FTS5表后没查到结果，大概率是因为创建虚拟表后没有同步原clients表的数据，需要执行INSERT INTO clients_fts SELECT businessName, companyName, name, businessNumber, email FROM clients;来初始化，但既然你不想用FTS5，就不用纠结这个了。

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