Did you try something like: SELECT id + 1 FROM foo WHERE id>= 100 AND id< 200 and id = MAX (id);
On 02/22/2010 7:02 AM, ArtemGr wrote: > Simon Slavin<slav...@...> writes: > >> Just out of interest, and I know that theoretically this is not an optimal >> statement, but have you compared >> this with the results of putting the '+1' in the brackets ? >> > Thanks for the pointer, Simon. > > Looking back to my analizys, I see that it was wrong. > SELECT MAX (id) FROM foo WHERE id>= 100 AND id< 200; > actually jumps to Close directly after AggStep, > it would only read a single row to produce the result. > > No suck luck for > SELECT MAX (id) + 1 FROM foo WHERE id>= 100 AND id< 200; > which goes thru the whole range. > > > As for MAX (id) + 1 versus MAX (id + 1), > > SELECT MAX (id) + 1 FROM foo WHERE id>= 100 AND id< 200; > calculates MAX (id) in a cycle, then it adds 1 outside of the cycle, > before submitting ResultRow. > > SELECT MAX (id + 1) FROM foo WHERE id>= 100 AND id< 200; > increments id in every iteration of the cycle and passes the result to max. > > MAX (id + 1) is clearly less optimizable than MAX (id) + 1. > Obvioulsy, SQLite already have a special case optimization for MAX (id), > but optimization breaks with MAX (id) + 1, making it impractical for use in > INSERT SELECT. > > _______________________________________________ > 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
