sqlite3 order by提姆

Question

我在sqlite3db中有一些表，它们位于内存映射分区上。我的select语句与此查询计划类似：

sqlite3> EXPLAIN QUERY PLAN SELECT sst.prefix, 0 AS pb, sst.rate, 
sst.rate_n, sst.interval_1, sst.interval_n
FROM sch AS sst
WHERE
sst.i_workbook_id = 989 AND 
sst.prefix IN ('', '1', '19', '191', '1919', '19198') AND 
sst.activation_date <= DATETIME('now') AND 
(sst.expiration_date > DATETIME('now') OR sst.expiration_date IS NULL) AND
sst.start_time <= TIME('now') AND 
sst.end_time >= TIME('now');


0|0|0|SCAN TABLE sch AS sst (~185 rows)
0|0|0|EXECUTE LIST SUBQUERY 1

现在，如果我添加order，那么他的查询计划将两次命中主表，所花的时间是以前的4倍，而表有130万行，但筛选的行只有两行。你知道吗

以下是新的查询计划：

sqlite3> EXPLAIN QUERY PLAN SELECT sst.prefix, 0 AS pb, sst.rate, 
sst.rate_n, sst.interval_1, sst.interval_n
FROM sch AS sst
WHERE
sst.i_workbook_id = 989 AND 
sst.prefix IN ('', '1', '19', '191', '1919', '19198') AND 
sst.activation_date <= DATETIME('now') AND 
(sst.expiration_date > DATETIME('now') OR sst.expiration_date IS NULL) AND
sst.start_time <= TIME('now') AND 
sst.end_time >= TIME('now') order by sst.prefix;     

0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|SEARCH TABLE sch AS sst USING AUTOMATIC COVERING INDEX (i_workbook_id=?) (~7 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|SEARCH TABLE sch AS sst USING AUTOMATIC COVERING INDEX (i_workbook_id=?) (~7 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR ORDER BY

有什么想法吗？我做错什么了？你知道吗

Answer 1

SQLite试图通过在i_workbook_id列上创建临时索引来优化第二个查询。 因为无法以更好的方式优化OR表达式，所以该查找被多次执行。 （参见query optimizer和query planner文档。）

你能做的就是

• 在i_workbook_id列上创建索引；或
• 在i_workbook_id和prefix列上创建索引；或
• 首先在这两个列上创建一个覆盖索引，然后在SELECT和WHERE子句中提到的所有其他列上创建一个覆盖索引。你知道吗