If there can be not more than one row that satisfies the constraints (i.e. the constraints specifiy a unique key) and there is an index (express or implied or autocreated) available, then a simple index lookup will suffice.
How many rows do you expect to have a rowid of 1? How many rows do you expect to read from table a for each row of table b if a_rowid is a foreign key? create temp table a (f1 integer); create temp table b (a_rowid integer, f2 integer); .explain explain select * from a where rowid = 1; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 NULL 1 Integer 1 1 0 00 NULL 2 Goto 0 10 0 00 NULL 3 OpenRead 0 2 1 1 00 a 4 MustBeInt 1 8 0 00 NULL 5 NotExists 0 8 1 00 pk 6 Column 0 0 3 00 a.f1 7 ResultRow 3 1 0 00 NULL 8 Close 0 0 0 00 NULL 9 Halt 0 0 0 00 NULL 10 Transaction 1 0 0 00 NULL 11 VerifyCookie 1 1 0 00 NULL 12 TableLock 1 2 0 a 00 NULL 13 Goto 0 3 0 00 NULL explain select * from a join b on a.rowid=b.a_rowid where b.f2 = 1; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 NULL 1 Integer 1 1 0 00 NULL 2 Goto 0 19 0 00 NULL 3 OpenRead 1 3 1 2 00 b 4 OpenRead 0 2 1 1 00 a 5 Rewind 1 16 0 00 NULL 6 Column 1 1 2 00 b.f2 7 Ne 1 15 2 collseq(BINARY) 6c NULL 8 Column 1 0 3 00 b.a_rowid 9 MustBeInt 3 15 0 00 NULL 10 NotExists 0 15 3 00 pk 11 Column 0 0 4 00 a.f1 12 Column 1 0 5 00 b.a_rowid 13 Column 1 1 6 00 b.f2 14 ResultRow 4 3 0 00 NULL 15 Next 1 6 0 01 NULL 16 Close 1 0 0 00 NULL 17 Close 0 0 0 00 NULL 18 Halt 0 0 0 00 NULL 19 Transaction 1 0 0 00 NULL 20 VerifyCookie 1 2 0 00 NULL 21 TableLock 1 3 0 b 00 NULL 22 TableLock 1 2 0 a 00 NULL 23 Goto 0 3 0 00 NULL -----Urspr?ngliche Nachricht----- Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com] Gesendet: Dienstag, 17. M?rz 2015 12:26 An: General Discussion of SQLite Database Betreff: [sqlite] regarding loops in vdbe code When joining a table in sqlite with some condition using where clause, sqlite sometimes generate less number of loops(Next opcodes) than the number of tables. Can anyone explain how sqlite iterates through all the tables when it has less number of loops. _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
