Sqlite3 select min, max together is much slower than selecting them separately

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 

While I use min and max separately, it works fine. However, sqlite will “forget” the index, while for some reason I choose min and max . Is there any configuration I can do (I already used Analyze , this will not work)? or are there any explanations for this behavior?

EDIT1

 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) 

Answer 1

This is the famous sqlite query optimizer quirk as described here: http://www.sqlite.org/optoverview.html#minmax :

Requests of the following forms will be optimized for work in logarithmic time, provided that the corresponding indices are available:

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

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

UPDATE (2017/06/23): Recently, this update has been updated to say that a query containing one MAX or MIN can be satisfied by looking at the index (given things like arithmetic); however, they still prevent you from having more than one such aggregation operator in a single query (so MIN, MAX will still be slow):

Queries containing a single function MIN () or MAX (), the argument of which is the leftmost column of the index, can be satisfied by performing a single index search, and not by scanning the entire table. Examples:

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