Sairam Gaddam wrote: > 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. > > Then why there is a loop (Next opcode at 23rd instruction) over second > table when it created an index ?
Because there might be multiple index entries with the same fame value. Regards, Clemens
