Let's create and populate a test table: sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY NOT NULL); sqlite> INSERT INTO foo VALUES (1); sqlite> INSERT INTO foo VALUES (100); sqlite> INSERT INTO foo VALUES (200); sqlite> INSERT INTO foo SELECT MAX (id) + 1 FROM foo WHERE id >= 100 AND id < 200; sqlite> INSERT INTO foo SELECT MAX (id) + 1 FROM foo WHERE id >= 100 AND id < 200; sqlite> INSERT INTO foo SELECT MAX (id) + 1 FROM foo WHERE id >= 100 AND id < 200;
Now, querying for MAX (id) in an id range gives, I think, a suboptimal plan, traversing all the entries in the foo between 200 and 100, but what is interesting is that it starts with the greatest id (SeekLt): sqlite> explain SELECT MAX (id) FROM foo WHERE id >= 100 AND id < 200; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Null 0 2 0 00 2 Null 0 1 0 00 3 Integer 100 3 0 00 4 Integer 200 4 0 00 5 Goto 0 21 0 00 6 OpenRead 0 2 0 0 00 7 SeekLt 0 16 4 00 8 SCopy 3 6 0 00 9 Rowid 0 5 0 00 10 Lt 6 16 5 6b 11 Rowid 0 7 0 00 12 CollSeq 0 0 0 collseq(BINARY) 00 13 AggStep 0 7 1 max(1) 01 14 Goto 0 16 0 00 15 Prev 0 9 0 00 16 Close 0 0 0 00 17 AggFinal 1 1 0 max(1) 00 18 SCopy 1 8 0 00 19 ResultRow 8 1 0 00 20 Halt 0 0 0 00 21 Transaction 0 0 0 00 22 VerifyCookie 0 3 0 00 23 TableLock 0 2 0 foo 00 24 Goto 0 6 0 00 Whereas querying for MAX (id) + 1 makes it start from the lowest id and go forward: sqlite> explain SELECT MAX (id) + 1 FROM foo WHERE id >= 100 AND id < 200; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Null 0 2 0 00 2 Null 0 1 0 00 3 Integer 100 3 0 00 4 Integer 200 4 0 00 5 Goto 0 21 0 00 6 OpenRead 0 2 0 0 00 7 SeekGe 0 15 3 00 8 SCopy 4 6 0 00 9 Rowid 0 5 0 00 10 Ge 6 15 5 6b 11 Rowid 0 7 0 00 12 CollSeq 0 0 0 collseq(BINARY) 00 13 AggStep 0 7 1 max(1) 01 14 Next 0 9 0 00 15 Close 0 0 0 00 16 AggFinal 1 1 0 max(1) 00 17 Integer 1 5 0 00 18 Add 5 1 8 00 19 ResultRow 8 1 0 00 20 Halt 0 0 0 00 21 Transaction 0 0 0 00 22 VerifyCookie 0 3 0 00 23 TableLock 0 2 0 foo 00 24 Goto 0 6 0 00 I wonder why it is. Of course, to get an optimal plan for this one would need to write SELECT id + 1 FROM foo WHERE id >= 100 AND id < 200 ORDER BY id DESC LIMIT 1; but I wonder if the difference between MAX (id) and MAX (id) + 1 is an unfinished attempt at a special case optimization for MAX. Also, it would be nice and intuitive if the MAX (id) and MAX (id) + 1 worked optimally within a range! _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
