On 14/07/2009 11:44 AM, Jay A. Kreibich 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.
Let's assume right from the start that there's going to be an index on the subject column. too_slow * 2 == too_slow in boss arithmetic :-) Are you sure? I'm no expert on decoding the EXPLAIN output but the two look essentially the same to me: set up a cursor on the index, do a SeekLt(the_input_parameter) then test the limit in the first case, do exactly ONE AggStep operation in the other case > > You also have the problem that you can't return the rest of the row. (1) RowS plural. The limit 1 is arbitrary; there may be more than one row with such a value of number. (2) I would have thought it possible to return the rest of the rows using something like this: select * from table t1 where t1.number = (select max(t2.number) from table t2 where t2.number <= ?); with optional LIMIT if desired. My rules of thumb: (1) a sub-select like that can be used just about everywhere (2) whenever I see "limit 1" I get nervous and want to make absolutely sure that the query isn't going to generate a zillion rows and throw all but one away, or generate 5 and throw 4 away when somebody has presumed incorrectly that there would be only one row not 5. BTW, has the OP thought about the end conditions (no such lower value, no such higher value)? Cheers, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
