为什么 SQLite 不自动将此查询分成两部分？
我有两张桌子要加入。 TABLE_A（包含列 a）和 TABLE_BC（包含列 b 和 c）。 TABLE_BC 上有一个条件。这两个表通过rowid 连接。
SELECT a, b, c
FROM main.TABLE_A
INNER JOIN main.TABLE_BC
WHERE (b > 10.0 AND c < 10.0)
ON main.TABLE_A.rowid = main.TABLE_BC.rowid
ORDER BY a;
或者：
SELECT a, b, c
FROM main.TABLE_A AS s1
INNER JOIN
(
SELECT rowid, b, c
FROM main.TABLE_BC
WHERE (b > 10.0 AND c < 10.0)
) AS s2
ON s1.rowid = s2.rowid
ORDER BY a;
我需要使用不同的 TABLE_As 执行此操作几次，但 TABLE_BC 不会更改。因此，我可以通过为查询的常量部分创建临时内存数据库 (mem) 来加快速度。
CREATE TABLE mem.cache AS
SELECT rowid, b, c
FROM main.TABLE_BC
WHERE (b > 10.0 AND c < 10.0);
接下来（很多）
SELECT a, b, c
FROM main.TABLE_A
INNER JOIN mem.cache ON main.TABLE_A.rowid = mem.cache.rowid
ORDER BY a;
我从上面的所有查询中得到了相同的结果集，但最后一个是迄今为止最快的一个。
我想避免将查询分成两部分。我希望 SQLite 能够自动执行此操作（至少在第二种情况下），但事实并非如此。
为什么？
I have two tables to join. TABLE_A (contains column a) and TABLE_BC (contains columns b and c).
There is a condition on TABLE_BC. The two tables are joined by rowid.
SELECT a, b, c
FROM main.TABLE_A
INNER JOIN main.TABLE_BC
WHERE (b > 10.0 AND c < 10.0)
ON main.TABLE_A.rowid = main.TABLE_BC.rowid
ORDER BY a;
Alternatively:
SELECT a, b, c
FROM main.TABLE_A AS s1
INNER JOIN
(
SELECT rowid, b, c
FROM main.TABLE_BC
WHERE (b > 10.0 AND c < 10.0)
) AS s2
ON s1.rowid = s2.rowid
ORDER BY a;
I need to do this a couple of times with different TABLE_As, but TABLE_BC does not change. I could therefore speed things up by creating a temporary in-memory database (mem) for the constant part of the query.
CREATE TABLE mem.cache AS
SELECT rowid, b, c
FROM main.TABLE_BC
WHERE (b > 10.0 AND c < 10.0);
followed by (many)
SELECT a, b, c
FROM main.TABLE_A
INNER JOIN mem.cache ON main.TABLE_A.rowid = mem.cache.rowid
ORDER BY a;
I get the same result set from all the queries above, but the last is by far the fastest one.
I would like to avoid splitting the query into two parts. I would expect SQLite to do that automatically (at least in the second scenario), but it does not.
Why?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
SQLite 在优化方面相当轻松。一般经验法则：
SmallTable Inner Join BigTable比相反的要快。话虽这么说，我想知道您的第一个查询是否会以以下形式运行得更快：
SQLite is pretty light on optimization. The general rule of thumb:
SmallTable Inner Join BigTableis faster than the reverse.That being said I wonder if your first query would run faster in the following form:
来自SQLite 用户邮件列表的回答：
Answer from the SQLite User Mailing List: