You define table em(ployee) to have the field "name" (which is defined as numeric affinity) as the primary key. This forces SQLite to create a unique index on this table.
You also define table lo(cation) to have the field "name" (which is defined as numeric affinity) as the primary key. You specify lo.name=em.name in your WHERE clause. SQLite query planner thinks: Q:Given a specific value from an em.name, how many rows would I expect to retrieve? A: Since "name" is a UNIQUE PRIMARY KEY the answer is "not more than one" The same logic holds for idv.id=mny.id So to generate all the rows of the result, SQLITE has to - LOOP over em - SEEK the corresponding lo entry - LOOP over idv - SEEK the corresponding mny entry Which means there are |em| * |idv| entries in the result set. -----Urspr?ngliche Nachricht----- Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com] Gesendet: Dienstag, 17. M?rz 2015 13:42 An: General Discussion of SQLite Database Betreff: 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 ___________________________________________ 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.
