A PRIMARY KEY is also UNIQUE. Lookup for equality by primary key can return at most 1 row, so there is no need for a loop.
-----Urspr?ngliche Nachricht----- Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com] Gesendet: Montag, 09. M?rz 2015 12:36 An: General Discussion of SQLite Database Betreff: Re: [sqlite] regarding loops in joins(VDBE) I am trying to understand how sqlite joins execute and this is just a simple example. Can you please elaborate a bit on the index look up part ? How is sqlite navigating the cursor to point to the rows of other table with out a loop ? How does index look up work without looping as indexes are also represented by cursor ? On Mon, Mar 9, 2015 at 4:37 PM, Hick Gunter <hick at scigames.at> wrote: > Are you trying to create tables with INTEGER PRIMARY KEY? You have to > write EXACTLY "integer primary key" (not case specific) to achieve this. > The em <=> lo join would probably profit from this. > > Your join specifies to compare em.name (a column with numeric > affinity) to idv.name (a column with text affinity); this will force > SQLite to convert one of the fields for the conversion. Is this what you > intended? > > Maybe you should try to "EXPLAIN QUERY PLAN" to find out what SQLite > plans to do. > > From a quick look at the VDBE code it is clearly choosing to scan your > em table and access the lo and idv tables via index lookup. If either > of the index lookups were not UNIQUE, there would be an inner loop to > resolve the join. > > Since em.name and lo.name are matching primary keys, why the two tables? > Which other entity has a lo(cation)? And how are you going to keep > that other entity from assigning conflicting keys (i.e. those already > used for em entries)? > > Are you sure the em(ployee)'s name is an integer? > > -----Urspr?ngliche Nachricht----- > Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com] > Gesendet: Montag, 09. M?rz 2015 11:40 > An: General Discussion of SQLite Database > Betreff: [sqlite] regarding loops in joins(VDBE) > > sir, > I have a join query with 3 tables and 2 conditions in the where > clause.But the program executes by opening single loop.Can any one > know how is executes? > > I have included the query and the vdbe program in the link below: > http://pastebin.com/aA3QSJ7w > _______________________________________________ > 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.
