我有一个简单的查询
SELECT * FROM "mytable" where col1="foo"
在大约0.5秒内解析(700 MB数据库文件的大约100'000行的大约100个结果)
但是,一旦我添加ORDER BY
它需要120秒。
SELECT * FROM "mytable" where col1="foo" ORDER BY col2
即使我限制结果如此
SELECT * FROM (SELECT * FROM "mytable" where col1="foo" LIMIT 1) ORDER BY col2
这需要120秒,尽管没有什么可以排序的。
唯一的例外是如果我用ORDER BY rowid
(而不是ORDER BY col2
)排序,或者我这样做(0.5秒):
SELECT * FROM "mytable" WHERE rowid IN (SELECT rowid FROM "mytable" WHERE col1="foo") ORDER BY col2
我VACUUM
编写了数据库,我检查了数据库的完整性(确定),这个问题仍然存在。我使用的是SQLite版本:3.7.7.1,在phpLITEadmin和我的PHP代码中都出现了减速。
编辑
EXPLAIN QUERY PLAN SELECT * FROM "mytable" WHERE col1="foo"
SELECTID |订单|从|细节
0 | 0 | 0 | SCAN TABLE mytable(~11345行)
EXPLAIN QUERY PLAN SELECT * FROM "mytable" WHERE col1="foo" ORDER BY col2
SELECTID |订单|从|细节
0 | 0 | 0 | SEARCH TABLE mytable使用AUTOMATIC COVERING INDEX(col1 =?)(~7行)
0 | 0 | 0 |按顺序使用TEMP B-TREE
解
好吧,似乎我们找到了解决方案:CREATE INDEX col1_idx ON“mytable”(col1)需要一些神秘的原因。 在col2上创建索引(要排序的列)没有区别(使用或不使用col2的索引排序120秒)。 对我来说似乎是一个SQLite错误,因为没有索引col1的查询结果很快(0.5秒)并且排序一个非索引行(=根本没有排序)不应该花费120秒。 即使对整个表SELECT * FROM“mytable”ORDER BY col2进行排序,SQLite也只需要7秒。
同样有趣的是:col1和col2的CREATE INDEX只用了1.5秒。 因此,即使假设这种放缓的原因是SQLite自动创建了查询的临时索引,它仍然无法解释SQLite需要如此长时间才能进行排序。
发布于 2019-02-21 10:38:49
因此,似乎SQLite错误地认为构建临时索引(自动覆盖索引)来运行查询而不是在内存中进行排序会更便宜。显然,为每个查询构建100,000行的索引并不是最优的查询计划。
一个明显的解决方案是在要执行查询/排序的列上添加索引。
CREATE INDEX col1_idx ON mytable (col1);
CREATE INDEX col2_idx ON mytable (col2);
https://stackoverflow.com/questions/-100008961
复制相似问题