SCAN in the query plan = Rewind...Next LOOP in opcodes SEARCH in the query plan = Column...Seek in opcodes
SQLite has determined that creating an automatic index on the referenced tables should be faster than performing a full table scan for the general case. asql> explain query plan select * from em,idv,mny,lo where em.name=lo.name and idv.id=mny.id; sele order from deta ---- ------------- ---- ---- 0 0 0 SCAN TABLE em (~1000000 rows) 0 1 3 SEARCH TABLE lo USING INDEX sqlite_autoindex_lo_1 (name=?) (~1 rows) 0 2 1 SCAN TABLE idv (~1000000 rows) 0 3 2 SEARCH TABLE mny USING INDEX sqlite_autoindex_mny_1 (id=?) (~1 rows) asql> explain select * from em,idv,mny,lo where em.name=lo.name and idv.id=mny.id; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 NULL 1 Goto 0 42 0 00 NULL 2 OpenRead 0 2 1 2 00 em 3 OpenRead 3 8 1 2 00 lo 4 OpenRead 4 9 1 Keyinfo(1,BINARY) 00 sqlite_autoindex_lo_1 5 OpenRead 1 4 1 2 00 idv 6 OpenRead 2 6 1 2 00 mny 7 OpenRead 5 7 1 Keyinfo(1,BINARY) 00 sqlite_autoindex_mny_1 8 Rewind 0 35 0 00 NULL 9 Column 0 0 1 00 em.name 10 IsNull 1 34 0 00 NULL 11 Affinity 1 1 0 d 00 NULL 12 SeekGe 4 34 1 1 00 NULL 13 IdxGE 4 34 1 1 01 NULL 14 IdxRowid 4 2 0 00 NULL 15 Seek 3 2 0 00 NULL 16 Rewind 1 34 0 00 NULL 17 Column 1 0 3 00 idv.id 18 IsNull 3 33 0 00 NULL 19 Affinity 3 1 0 d 00 NULL 20 SeekGe 5 33 3 1 00 NULL 21 IdxGE 5 33 3 1 01 NULL 22 IdxRowid 5 4 0 00 NULL 23 Seek 2 4 0 00 NULL 24 Column 0 0 5 00 em.name 25 Column 0 1 6 00 em.age 26 Column 1 0 7 00 idv.id 27 Column 1 1 8 00 idv.name 28 Column 5 0 9 00 mny.id 29 Column 2 1 10 00 mny.sal 30 Column 4 0 11 00 lo.name 31 Column 3 1 12 00 lo.addr 32 ResultRow 5 8 0 00 NULL 33 Next 1 17 0 01 NULL 34 Next 0 9 0 01 NULL 35 Close 0 0 0 00 NULL 36 Close 3 0 0 00 NULL 37 Close 4 0 0 00 NULL 38 Close 1 0 0 00 NULL 39 Close 2 0 0 00 NULL 40 Close 5 0 0 00 NULL 41 Halt 0 0 0 00 NULL 42 Transaction 1 0 0 00 NULL 43 VerifyCookie 1 4 0 00 NULL 44 TableLock 1 2 0 em 00 NULL 45 TableLock 1 8 0 lo 00 NULL 46 TableLock 1 4 0 idv 00 NULL 47 TableLock 1 6 0 mny 00 NULL 48 Goto 0 2 0 00 NULL -----Ursprüngliche Nachricht----- Von: Sairam Gaddam [mailto:gaddamsai...@gmail.com] Gesendet: Montag, 02. Februar 2015 16:24 An: sqlite-users@sqlite.org Betreff: [sqlite] regarding looping in vdbe for sqlite table joins! Normally for executing joins in sqlite,the vdbe program opens 1 loop for each and every table but in my code(attached that file via pastebin) ,i am facing an issue because it is opening only 2 loops even if i use 4 tables in joining operation. can anyone explain why it happened like that and loops for which tables got opened? code : http://pastebin.com/PHV4K4Hh _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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: h...@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. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
