SQLite Select 查询性能调优

Question

我有以下查询，它只从一个表中获取数据。

编辑:这是一个查询，应该为自动完成功能返回数据。

1. 自动完成数据可以在 text1 中或 text2 .
2. 完全匹配的内容应该在最上面。 int3是一个整数权重值，结果的排序以此为准。此处的前两个查询用于识别完全匹配项。
3. 接下来的两个查询用于识别近似匹配项。 WHERE text1 > 'foo' AND text1 < 'fop'短语实际上等于WHERE text1 LIKE 'foo%' .我这样写是为了受益于索引。

希望这对您有所帮助。

SELECT DISTINCT text1 as Key, 'text1' as Source, int1 as Count, 1000 as int3 
   FROM mytable 
   WHERE text1 = 'foo' 

UNION SELECT DISTINCT text2 as Key, 'text2' as Source, int2 as Count, 1000 as int3 
   FROM mytable 
   WHERE text2 = 'foo' 

UNION SELECT text1 as Key, 'text1' as Source, int1 as Count, MAX(int3) as int3 
   FROM mytable 
   WHERE text1 > 'foo' AND text1 < 'fop' AND Count < 4 
   GROUP BY Key 

UNION SELECT text2 as Key, 'text2' as Source, int2 as Count, MAX(int3) as int3 
   FROM mytable 
   WHERE text2 > 'foo' AND text2 < 'fop' AND Count < 4 
   GROUP BY Key 

ORDER BY int3 DESC, Count, Key LIMIT 0, 15;

表结构为:

CREATE TABLE mytable (text1 TEXT, text2 TEXT, 
                      int1 NUMERIC, int2 NUMERIC, int3 NUMERIC);

它工作正常，但我需要微调性能。我尝试了不同的索引选项并使用内置计时器检查了性能结果。

我发现的最佳索引选项是:

CREATE INDEX cmp1 ON mytable (text1 ASC, int1 ASC, int3 DESC);
CREATE INDEX cmp2 ON mytable (text2 ASC, int2 ASC, int3 DESC);

如果您能向我展示任何更好的索引选项或性能更好的 SQL 查询，我将非常高兴。

Answer 1

UNION 删除出现在两个子查询中的重复行，因此必须为结果创建一个临时表。

如果您可以保证子查询是不同的，或者如果您不关心这些重复项，请改用 UNION ALL。

(在您的情况下，按非索引值 int3 排序无论如何都需要一个临时表。)

---

要优化子查询，请使用 EXPLAIN QUERY PLAN 运行它们检查他们是否使用索引。

如果您确保正确声明索引，则可以使用LIKE；见LIKE optimization .

参见 Query Planning有关如何构建索引的说明。你的两个指标似乎是最优的。