Try the following

sqlite> EXPLAIN QUERY PLAN SELECT Min(a) FROM t UNION ALL SELECT Max(a) FROM t;
selectid|order|from|detail
1|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows)
2|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

Should be a lot faster than a single query without UNION.

If you want the results in separate columns, you can do something like

SELECT Min(a) minimum, 'none' maximum FROM t UNION ALL SELECT 'none' minimum, 
Max(a) minimum FROM t;

On Apr 13, 2012, at 2:44 PM, Steinar Midtskogen wrote:

> Alessandro Marzocchi <alessandro.marzoc...@gmail.com> writes:
> 
>> What does EXPLAIN QUERY PLAN says?
> 
> sqlite> EXPLAIN QUERY PLAN SELECT min(unix_time) FROM table;
> 0|0|0|SEARCH TABLE table USING INTEGER PRIMARY KEY (~1 rows)
> 
> sqlite> EXPLAIN QUERY PLAN SELECT max(unix_time) FROM table;
> 0|0|0|SEARCH TABLE table USING INTEGER PRIMARY KEY (~1 rows)
> 
> sqlite> EXPLAIN QUERY PLAN SELECT min(unix_time), max(unix_time) FROM table;
> 0|0|0|SCAN TABLE table (~1000000 rows)
> 
> I suppose a query for a single min/max gets optimised, while a query
> involving multiple columns doesn't.
> 
> I have a much bigger table as well, and on that one the speedup is in
> the millions to run two SELECTs.  It's hard to guess that there will
> be such a difference, but I suppose I should be happy that there is at
> least an optimised way to get min and max for the integer primary key.
> 
> -- 
> Steinar
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to