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 >
