Jay, you're pretty much mistaken: > I'm pretty sure you don't want to do it this way. What this does is > gather every row that meets the WHERE condition and then runs a max() > or min() aggregation function across all of those rows. That means > that even if the column "number" has an index on it, between these > two statements you're going to end up scanning the whole table.
All database engines optimize queries which ask for min/max on indexed column with condition including only <, > or = on this very column. And SQLite is among these too: > rm test.db > ( echo "create table t (i integer); begin;"; for ((i = 0;i<10000000;++i)); do > echo "insert into t values ($i);"; done; echo "end; create index t_i on t > (i);" ) | sqlite3 test.db > # First force the full table scan > time sqlite3 test.db "select count(*) from t where i * i < 50000000000;" 223607 real 0m1.610s user 0m1.469s sys 0m0.125s > # now using index > time sqlite3 test.db "select * from t where i < 5000000 order by i desc limit > 1;" 4999999 real 0m0.043s user 0m0.001s sys 0m0.005s > time sqlite3 test.db "select max(i) from t where i < 5000000;" 4999999 real 0m0.005s user 0m0.001s sys 0m0.001s As you see using max() works better than "order by ... limit 1". But of course your other point is true - if you want some other data from table along with min/max value, you need to make additional select in case of using min/max. Pavel On Mon, Jul 13, 2009 at 9:44 PM, Jay A. Kreibich<j...@kreibi.ch> wrote: > On Mon, Jul 13, 2009 at 07:40:48PM -0400, Wes Freeman scratched on the wall: >> Yeah, sorry about that. In two statements: >> >> select max(number) from table where number < ? >> select min(number) from table where number > ? > > I'm pretty sure you don't want to do it this way. What this does is > gather every row that meets the WHERE condition and then runs a max() > or min() aggregation function across all of those rows. That means > that even if the column "number" has an index on it, between these > two statements you're going to end up scanning the whole table. > > You also have the problem that you can't return the rest of the row. > The min() and max() functions will extract the right answer, but > something like this will not: > > SELECT min(number), other_column, FROM table WHERE number > ? > > In that case, it is VERY likely that the value of "other_column" will > not come from the same row as "min(number)". > > For example: > > sqlite> create table t ( i integer, s char ); > sqlite> insert into t values (1, 'a'); > sqlite> insert into t values (2, 'b'); > sqlite> insert into t values (3, 'c'); > sqlite> select min(i), s from t; > 1|c > sqlite> > > This is because the min() and max() aggregations imply a GROUP BY. > Since none is given, the whole result is grouped. That works fine > for min() and max() and gives you the right answer, but any other > column you specify is simply going to return the value for the last > row processed. That's why the above example returns 'c' for the > second column. > > >> >>> select * from table where number <= ? order by number desc limit 1; >> >>> select * from table where number>= ? order by number asc limit 1; > > This is the best solution, especially if the "number" column has an > index on it. In that case the correct row can be extracted directly > from the next and results are nearly instant, no matter how large the > table is. > > As others have pointed out, you do, of course, want to use < and >, > and not <= and >=. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs. We have > a protractor." "I'll go home and see if I can scrounge up a ruler > and a piece of string." --from Anathem by Neal Stephenson > _______________________________________________ > 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
