> 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.
Even not all kind of MAX(id) queries are optimized: http://www.sqlite.org/optoverview.html#minmax Pavel On Mon, Feb 22, 2010 at 10:02 AM, ArtemGr <artem...@gmail.com> 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
