使用sqlite3同时选择最小值和最大值比分别选择它们要慢得多。

Question

sqlite> explain query plan select max(utc_time) from RequestLog;
0|0|0|SEARCH TABLE RequestLog USING COVERING INDEX key (~1 rows) # very fast

sqlite> explain query plan select min(utc_time) from RequestLog;
0|0|0|SEARCH TABLE RequestLog USING COVERING INDEX key (~1 rows) # very fast

sqlite> explain query plan select min(utc_time), max(utc_time) from RequestLog;
0|0|0|SCAN TABLE RequestLog (~8768261 rows) # will be very very slow

当我分别使用min和max时，它们都能正常工作。然而，由于某种原因，当我同时选择min和max时，sqlite会“忘记”索引。我是否可以进行任何配置（我已经使用了Analyze，但它不起作用）？或者对于这种行为有什么解释吗？

编辑1

sqlite> .schema 

CREATE TABLE FixLog(
                    app_id text,  __key__id INTEGER,
                    secret text, trace_code text, url text,
                    action text,facebook_id text,ip text,
                    tw_time datetime,time datetime,
                    tag text,to_url text,
                    from_url text,referer text,weight integer,
                    Unique(app_id, __key__id)
                    );
CREATE INDEX key4 on FixLog(action);
CREATE INDEX time on FixLog(time desc);
CREATE INDEX tw_time on FixLog(tw_time desc);



sqlite> explain query select min(time) from FixLog;
0|0|0|SEARCH TABLE FixLog USING COVERING INDEX time (~1 rows)
sqlite> explain query select max(time) from FixLog;
0|0|0|SEARCH TABLE FixLog USING COVERING INDEX time (~1 rows)
sqlite> explain query plan select max(time), min(time) from FixLog;
0|0|0|SCAN TABLE FixLog (~1000000 rows)

- lucemia

---

你能添加索引定义吗？没有它很难弄清楚发生了什么。 - Timo Geusch

我现在已经添加了索引信息。 - lucemia

Answer 1

这是sqlite查询优化器的已知问题，详细解释请参见：http://www.sqlite.org/optoverview.html#minmax。

Queries of the following forms will be optimized to run in logarithmic time assuming appropriate indices exist:

 SELECT MIN(x) FROM table;
 SELECT MAX(x) FROM table;

In order for these optimizations to occur, they must appear in exactly the form shown above - changing only the name of the table and column. It is not permissible to add a WHERE clause or do any arithmetic on the result. The result set must contain a single column. The column in the MIN or MAX function must be an indexed column.

更新（2017/06/23）：最近，这一点已经更新，称包含单个MAX或MIN的查询可能通过索引查找得到满足（允许诸如算术之类的东西）; 然而，他们仍然禁止在单个查询中使用多个聚合操作符（因此，MIN、MAX仍然很慢）：

Queries that contain a single MIN() or MAX() aggregate function whose argument is the left-most column of an index might be satisfied by doing a single index lookup rather than by scanning the entire table. Examples:

SELECT MIN(x) FROM table;
SELECT MAX(x)+1 FROM table;

- nneonneo