SQLite选择查询性能调优

Question

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

编辑： 这是一条用于返回自动完成功能数据的查询。

1. 自动完成数据可以是在文本1或文本2中。 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查询，我会感到很高兴。

- noway

---

首先指定您的查询要获取哪些数据会很有帮助。 - Tim

查询比较复杂，简要解释一下你想要实现什么可能会帮助人们更快地理解和回答问题。 - Zut

@Tim 你说得对。抱歉，我已经更新了问题。 - noway

Answer 1

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

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

（在您的情况下，对非索引值int3进行排序仍然需要创建一个临时表。）

---

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

如果确保正确声明了索引，则可以使用LIKE；请参见LIKE optimization。

有关如何构建索引的说明，请参见Query Planning。您的两个索引似乎是最优的。

- CL.

---

+1 谢谢，我会查看 UNION ALL。顺便说一下，我已经更新了问题。 - noway