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
