Looks correct to me. There are only two loops required. One to loop through em (which will only find at most one matching row in lo, so no need for a loop there), and another to loop through mny (which will find at most one matching row in ldv, so no need to loop there). The cross-product of those two loops is then returned.
SQLite version 3.8.9 2015-03-16 20:40:00 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table em(name int primary key,age int); sqlite> create table idv(id int primary key,name text); sqlite> create table mny(id int primary key,sal int); sqlite> create table lo(name int primary key,addr text); sqlite> sqlite> insert into em values(44,21); sqlite> insert into em values(11,20); sqlite> insert into em values(5,20); sqlite> insert into idv values(11,44); sqlite> insert into idv values(5,11); sqlite> insert into idv values(44,180); sqlite> insert into mny values(5,10000); sqlite> insert into mny values(11,5000); sqlite> insert into mny values(44,5000); sqlite> insert into lo values(5,'NY'); sqlite> insert into lo values(44,'che'); sqlite> insert into lo values(11,'NY'); sqlite> sqlite> .eqp on sqlite> select * from em,lo,mny,idv where lo.name=em.name and idv.id=mny.id; --EQP-- 0,0,0,SCAN TABLE em --EQP-- 0,1,1,SEARCH TABLE lo USING INDEX sqlite_autoindex_lo_1 (name=?) --EQP-- 0,2,2,SCAN TABLE mny --EQP-- 0,3,3,SEARCH TABLE idv USING INDEX sqlite_autoindex_idv_1 (id=?) 44|21|44|che|5|10000|5|11 44|21|44|che|11|5000|11|44 44|21|44|che|44|5000|44|180 11|20|11|NY|5|10000|5|11 11|20|11|NY|11|5000|11|44 11|20|11|NY|44|5000|44|180 5|20|5|NY|5|10000|5|11 5|20|5|NY|11|5000|11|44 5|20|5|NY|44|5000|44|180 sqlite> sqlite> .explain sqlite> explain select * from em,lo,mny,idv where lo.name=em.name and idv.id=mny.id; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 55 0 00 Start at 55 1 OpenRead 0 2 0 2 00 root=2 iDb=0; em 2 OpenRead 1 8 0 2 00 root=8 iDb=0; lo 3 OpenRead 4 9 0 k(2,nil,nil) 00 root=9 iDb=0; sqlite_autoindex_lo_1 4 OpenRead 2 6 0 2 00 root=6 iDb=0; mny 5 OpenRead 3 4 0 2 00 root=4 iDb=0; idv 6 OpenRead 5 5 0 k(2,nil,nil) 00 root=5 iDb=0; sqlite_autoindex_idv_1 7 Explain 0 0 0 SCAN TABLE em 00 8 Noop 0 0 0 00 Begin WHERE-loop0: em 9 Rewind 0 47 0 00 10 Explain 0 1 1 SEARCH TABLE lo USING INDEX sqlite_autoindex_lo_1 (name=?) 00 11 Noop 0 0 0 00 Begin WHERE-loop1: lo 12 Column 0 0 1 00 r[1]=em.name 13 IsNull 1 45 0 00 if r[1]==NULL goto 45 14 Affinity 1 1 0 D 00 affinity(r[1]) 15 SeekGE 4 45 1 1 00 key=r[1] 16 IdxGT 4 45 1 1 00 key=r[1] 17 IdxRowid 4 2 0 00 r[2]=rowid 18 Seek 1 2 0 00 intkey=r[2] 19 Explain 0 2 2 SCAN TABLE mny 00 20 Noop 0 0 0 00 Begin WHERE-loop2: mny 21 Rewind 2 44 0 00 22 Explain 0 3 3 SEARCH TABLE idv USING INDEX sqlite_autoindex_idv_1 (id=?) 00 23 Noop 0 0 0 00 Begin WHERE-loop3: idv 24 Column 2 0 3 00 r[3]=mny.id 25 IsNull 3 42 0 00 if r[3]==NULL goto 42 26 Affinity 3 1 0 D 00 affinity(r[3]) 27 SeekGE 5 42 3 1 00 key=r[3] 28 IdxGT 5 42 3 1 00 key=r[3] 29 IdxRowid 5 4 0 00 r[4]=rowid 30 Seek 3 4 0 00 intkey=r[4] 31 Noop 0 0 0 00 Begin WHERE-core 32 Column 0 0 5 00 r[5]=em.name 33 Column 0 1 6 00 r[6]=em.age 34 Column 4 0 7 00 r[7]=lo.name 35 Column 1 1 8 00 r[8]=lo.addr 36 Column 2 0 9 00 r[9]=mny.id 37 Column 2 1 10 00 r[10]=mny.sal 38 Column 5 0 11 00 r[11]=idv.id 39 Column 3 1 12 00 r[12]=idv.name 40 ResultRow 5 8 0 00 output=r[5..12] 41 Noop 0 0 0 00 End WHERE-core 42 Noop 0 0 0 00 End WHERE-loop3: idv 43 Next 2 22 0 01 44 Noop 0 0 0 00 End WHERE-loop2: mny 45 Noop 0 0 0 00 End WHERE-loop1: lo 46 Next 0 10 0 01 47 Noop 0 0 0 00 End WHERE-loop0: em 48 Close 0 0 0 00 49 Close 1 0 0 00 50 Close 4 0 0 00 51 Close 2 0 0 00 52 Close 3 0 0 00 53 Close 5 0 0 00 54 Halt 0 0 0 00 55 Transaction 0 0 4 0 01 56 TableLock 0 2 0 em 00 iDb=0 root=2 write=0 57 TableLock 0 8 0 lo 00 iDb=0 root=8 write=0 58 TableLock 0 6 0 mny 00 iDb=0 root=6 write=0 59 TableLock 0 4 0 idv 00 iDb=0 root=4 write=0 60 Goto 0 1 0 00 sqlite> --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-----Original Message----- >From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >bounces at mailinglists.sqlite.org] On Behalf Of Sairam Gaddam >Sent: Tuesday, 17 March, 2015 06:42 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] regarding loops in vdbe code > >But in my example there are multiple rows which satisfy the condition. > >My example: > >sql="create table em(name int primary key,age int);"\ > "create table idv(id int primary key,name text);"\ > "create table mny(id int primary key,sal int);"\ > "create table lo(name int primary key,addr text);"; > >sql="insert into em values(44,21);"\ > "insert into em values(11,20);"\ >"insert into em values(5,20);"\ > "insert into idv values(11,44);"\ > "insert into idv values(5,11);"\ >"insert into idv values(44,180);"\ > "insert into mny values(5,10000);"\ > "insert into mny values(11,5000);"\ >"insert into mny values(44,5000);"\ > "insert into lo values(5,'NY');"\ > "insert into lo values(44,'che');"\ >"insert into lo values(11,'NY');"; > >sql="select * from em,lo,mny,idv where lo.name=em.name and idv.id=mny.id >"; > >VDBE: > > 0 Init 0 41 0 00 > 1 OpenRead 0 2 0 2 00 > 2 OpenRead 1 9 0 2 00 > 3 OpenRead 4 10 0 k(2,nil,nil) 00 > 4 OpenRead 2 7 0 2 00 > 5 OpenRead 3 5 0 2 00 > 6 OpenRead 5 6 0 k(2,nil,nil) 00 > 7 Rewind 0 34 0 00 > 8 Column 0 0 1 00 > 9 IsNull 1 33 0 00 > 10 Affinity 1 1 0 D 00 > 11 SeekGE 4 33 1 1 00 > 12 IdxGT 4 33 1 1 00 > 13 IdxRowid 4 2 0 00 > 14 Seek 1 2 0 00 > 15 Rewind 2 33 0 00 > 16 Column 2 0 3 00 > 17 IsNull 3 32 0 00 > 18 Affinity 3 1 0 D 00 > 19 SeekGE 5 32 3 1 00 > 20 IdxGT 5 32 3 1 00 > 21 IdxRowid 5 4 0 00 > 22 Seek 3 4 0 00 > 23 Column 0 0 5 00 > 24 Column 0 1 6 00 > 25 Column 4 0 7 00 > 26 Column 1 1 8 00 > 27 Column 2 0 9 00 > 28 Column 2 1 10 00 > 29 Column 5 0 11 00 > 30 Column 3 1 12 00 > 31 ResultRow 5 8 0 00 > 32 Next 2 16 0 01 > 33 Next 0 8 0 01 > 34 Close 0 0 0 00 > 35 Close 1 0 0 00 > 36 Close 4 0 0 00 > 37 Close 2 0 0 00 > 38 Close 3 0 0 00 > 39 Close 5 0 0 00 > 40 Halt 0 0 0 00 > 41 Transaction 0 0 44729 0 01 > 42 TableLock 0 2 0 em 00 > 43 TableLock 0 9 0 lo 00 > 44 TableLock 0 7 0 mny 00 > 45 TableLock 0 5 0 idv 00 > 46 Goto 0 1 0 00 > > >How sqlite works in this case ? >because there are 4 tables and for only 2 tables(or indices) it opened >loops ? >The next opcodes belongs to which tables in this case? > > >On Tue, Mar 17, 2015 at 5:47 PM, Hick Gunter <hick at scigames.at> wrote: > >> 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. >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >_______________________________________________ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
