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
>
