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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
