On 4/14/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > EXPLAIN is your friend. As can be seen by EXPLAINing each query (see below), > there are fewer instructions involved in the one with the subquery, and no > sorts or loops as are done in the initial method.
Well, not everybody is a specialist on SQLite bytecode, I guess. It looks a lot like RISC code to me, which means that it is mostly illegible :-) Anyway, I have compared statements with identical functions, and my result is rather different. I don't know about loops, and I can't do a real performance test at the moment, but at least the bytecode is a lot longer for the subquery (as I would expect). I did: sqlite> CREATE TABLE test (id INTEGER, addr CHAR(10), rest CHAR(20)); sqlite> CREATE INDEX testindex ON test (addr,id); sqlite> EXPLAIN SELECT rest FROM test WHERE id>100 AND addr='1234' ORDER BY id LIMIT 1; ... 39 sqlite> EXPLAIN SELECT rest FROM test WHERE id=(SELECT min(id) FROM test WHERE id>100 AND addr='1234') AND addr='1234'; ... 72 So which one is more efficient? Adding LIMIT 1 to the second one adds 5 more bytecodes (weird?). That having said SQLite seems to match the performance of MySQL pretty well in this task. I am positively surprised. Thomas (Note that I've changed the > maximum value to what fits in a signed 32-bit field since I'm doing this with > 2.8.16. You could try a similar experiment with 3.0.x. Actually, it looks > like you don't even need the WHERE clause in the original query, and I've > tested that modification at the end of the EXPLAINation below, as well. > > > What if id is not unique, and I may have rows with identical id? > > The original poster had 'id' as INTEGER PRIMARY KEY so that wouldn't be > possible. If it is possible, then you'd have to decide what you wanted to do > with multiple results. > > % sqlite :memory: > SQLite version 2.8.16 > Enter ".help" for instructions > sqlite> CREATE TABLE Mactor > ...> ( > ...> id INTEGER PRIMARY KEY, > ...> name TEXT, > ...> -- any other fields > ...> comment TEXT > ...> ); > sqlite> explain SELECT * FROM Mactor WHERE id < 2147483647 ORDER BY id > DESC LIMIT 1; > addr|opcode|p1|p2|p3 > 0|ColumnName|0|0|id > 1|ColumnName|1|0|name > 2|ColumnName|2|1|comment > 3|Integer|-1|0| > 4|MemStore|0|1| > 5|ColumnName|3|0|INTEGER > 6|ColumnName|4|0|TEXT > 7|ColumnName|5|0|TEXT > 8|Integer|0|0| > 9|OpenRead|0|3|Mactor > 10|VerifyCookie|0|31| > 11|Rewind|0|25| > 12|Integer|2147483647|0|2147483647 > 13|MemStore|1|1| > 14|Recno|0|0| > 15|MemLoad|1|0| > 16|Ge|0|25| > 17|Recno|0|0| > 18|Column|0|1| > 19|Column|0|2| > 20|SortMakeRec|3|0| > 21|Recno|0|0| > 22|SortMakeKey|1|0|- > 23|SortPut|0|0| > 24|Next|0|14| > 25|Close|0|0| > 26|Sort|0|0| > 27|SortNext|0|32| > 28|MemIncr|0|31| > 29|SortCallback|3|0| > 30|Goto|0|27| > 31|Pop|1|0| > 32|SortReset|0|0| > 33|Halt|0|0| > sqlite> explain SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM > Mactor); > addr|opcode|p1|p2|p3 > 0|VerifyCookie|0|31| > 1|Integer|0|0| > 2|OpenRead|1|3|Mactor > 3|Last|1|0| > 4|Recno|1|0| > 5|MemStore|0|1| > 6|Goto|0|7| > 7|Close|1|0| > 8|ColumnName|0|0|id > 9|ColumnName|1|0|name > 10|ColumnName|2|1|comment > 11|ColumnName|3|0|INTEGER > 12|ColumnName|4|0|TEXT > 13|ColumnName|5|0|TEXT > 14|Integer|0|0| > 15|OpenRead|0|3|Mactor > 16|MemLoad|0|0| > 17|MustBeInt|1|23| > 18|NotExists|0|23| > 19|Recno|0|0| > 20|Column|0|1| > 21|Column|0|2| > 22|Callback|3|0| > 23|Close|0|0| > 24|Halt|0|0| > sqlite>explain SELECT * FROM Mactor ORDER BY id DESC LIMIT 1; > addr|opcode|p1|p2|p3 > 0|ColumnName|0|0|id > 1|ColumnName|1|0|name > 2|ColumnName|2|1|comment > 3|Integer|-1|0| > 4|MemStore|0|1| > 5|ColumnName|3|0|INTEGER > 6|ColumnName|4|0|TEXT > 7|ColumnName|5|0|TEXT > 8|Integer|0|0| > 9|OpenRead|0|3|Mactor > 10|VerifyCookie|0|31| > 11|Rewind|0|20| > 12|Recno|0|0| > 13|Column|0|1| > 14|Column|0|2| > 15|SortMakeRec|3|0| > 16|Recno|0|0| > 17|SortMakeKey|1|0|- > 18|SortPut|0|0| > 19|Next|0|12| > 20|Close|0|0| > 21|Sort|0|0| > 22|SortNext|0|27| > 23|MemIncr|0|26| > 24|SortCallback|3|0| > 25|Goto|0|22| > 26|Pop|1|0| > 27|SortReset|0|0| > 28|Halt|0|0| > sqlite> >
