Then why there is a loop (Next opcode at 23rd instruction) over second table when it created an index ?
On Thu, Apr 9, 2015 at 1:04 PM, Clemens Ladisch <clemens at ladisch.de> wrote: > Sairam Gaddam wrote: > > sql="create table em(name text primary key,age text,pts text);"\ > > "create table l(name text primary key,fame text);"; > > > > sql = "select * from em,l where l.fame=em.age"; > > > > 4 Once 0 13 0 00 > > 5 OpenAutoindex 2 3 0 k(3,nil,nil,nil) 00 > > 6 Rewind 1 13 0 00 > > 7 Column 1 1 2 00 > > 8 Column 1 0 3 00 > > 9 Rowid 1 4 0 00 > > 10 MakeRecord 2 3 1 00 > > 11 IdxInsert 2 1 0 10 > > 12 Next 1 7 0 03 > > 13 Column 0 1 5 00 > > 14 IsNull 5 24 0 00 > > 15 SeekGE 2 24 5 1 00 > > 16 IdxGT 2 24 5 1 00 > > 17 Column 0 0 6 00 > > 18 Copy 5 7 0 00 > > 19 Column 0 2 8 00 > > 20 Column 2 1 9 00 > > 21 Column 2 0 10 00 > > 22 ResultRow 6 5 0 00 > > 23 Next 2 16 0 00 > > 24 Next 0 4 0 01 > > ... > > > > whenever the condition in the where clause is false, the program jumps to > > the instruction pointed by p2 of SeekGe > > Yes. > > > but if the condition proves to be false for the row 1 of both the > > tables, then the program jumps to line 24(in this case) which > > corresponds to outer table and takes the second row of outer table > > for next iteration, then when will the program fetch 1st row > > of table-1 and remaining rows of table-2 ??? > > In the join loop, this VDBE program does not fetch any rows from the > second table: > > explain query plan select * from em,l where l.fame=em.age; > 0|0|0|SCAN TABLE em > 0|1|1|SEARCH TABLE l USING AUTOMATIC COVERING INDEX (fame=?) > > All accesses to "l" are actually handled by the temporary index (which > is created by instructions 5..12). One index search is enough to > determine whether a fame value exists. > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
