Try: [ select distinct a.playerid, a.name, d.value, b.name, d currvalue from a join d on a.playerid=d.playerid join b on a.teamid=b.teamid where d.id=1 ]
Indexes should be created for WHERE clauses, not JOIN. On Thu, Mar 7, 2013 at 3:59 AM, Igor Korot <ikoro...@gmail.com> wrote: > Hi, Ryan, > > On Fri, Mar 1, 2013 at 12:48 PM, Ryan Johnson > <ryan.john...@cs.utoronto.ca> wrote: > > On 01/03/2013 2:23 PM, Igor Korot wrote: > >> > >> Hi, guys, > >> > >> On Fri, Mar 1, 2013 at 8:48 AM, Ryan Johnson > >> <ryan.john...@cs.utoronto.ca> wrote: > >>> > >>> On 01/03/2013 11:10 AM, Stephen Chrzanowski wrote: > >>>> > >>>> ***I'm waiting for the repair man to show up to fix my waterheater... > >>>> so... > >>>> I'm bored. This is going to be to the point at the beginning, but get > >>>> wordy > >>>> and technical near the end. ;) Super over kill..... ahem**** > >>> > >>> Nice explanation... just a couple of nitpicks: > >> > >> Very nice indeed. > >> Here is what I'm trying to with little more detail. > >> > >> I am using C API in a C++ based program. > >> What I'm trying to do is decrease the time the program displays the > >> main frame window. > >> > >> Basically during construction I am calling SELECT ... WHERE > >> <table1>.PK = <table2>.FK. > >> Problem is when <table2> was created I didn't make the foreign key. > > > > Check the output of `explain query plan' but I suspect the join already > uses > > an index on <table1>.PK (if that PK was really declared as such, there > will > > be an index on it). The foreign key (or lack thereof) has nothing to do > with > > this situation. > > > > Depending on what conditions you have in the where clause, some > additional > > indexes could be helpful. For example, if you want "where <table2>.z > between > > :X and :Y" (where X and Y are parameters passed in from your code), then > an > > index on <table2>.c will speed things up drastically because it will > allow > > fetching only the range of records that match the predicate. The > improvement > > is even more pronounced for equality predicates. As a completely bogus > > example: > > > > create table foo(x integer primary key, y, z); > > create table bar(a integer primary key, b, c, x integer); > > explain query plan select * from foo join bar using(x) where z between 30 > > and 50 and c = 10; > >> > >> 0|0|1|SCAN TABLE bar (~100000 rows) > >> 0|1|0|SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) > > > > > > create index foo_z on foo(z); > > create index bar_c on bar(c); > > explain query plan select * from foo join bar using(x) where z between 30 > > and 50 and c = 10; > >> > >> 0|0|1|SEARCH TABLE bar USING INDEX bar_c (c=?) (~10 rows) > >> 0|1|0|SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) > > Let me give an example so that you can better understand the problem: > > create table a(playerid integer primary key, name varchar(70), teamid > integer, value double, foreign key teamid references b(teamid)); > create table b(teamid integer primary key, teamname varchar(15)); > create table c(id integer primary key, name varchar(10)); > create table d(id integer, playerid integer, value integer, currvalue > double); > > SELECT DISTINCT a.playerid, a.name, d.value, b.name, d.currvalue FROM > a, b, d WHERE d.playerid = a.playerid AND a.teamid = b.teamid AND d.id > = 1; > > Without any indexes I am getting: > > 0|0|2|SCAN TABLE d (~100000 rows) > 0|1|0|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) > 0|2|1|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) > 0|0|0|USE TEMP B-TREE FOR DISTINCT > > What I am thinking is to create an index on the d for the playerid + > id to speed things up. > > Am I right? Any suggestions for improvements? > > Now in the end of my program I will update the d table - value and > currvalue fields - for every playerid that > belongs to the id = 1. > > Will this index help me there as well or I will lose performance? > > Thank you. > > > > > > > The row estimates are worthless (it's an empty database and I never ran > > ANALYZE) but you get the idea: without an index, it scans all of bar for > > matching rows (= slow) and then joins it to foo using the latter's > primary > > key index. With the index, it probes bar_c for precisely the data needed > (= > > fast), then uses foo's primary key index to complete the join as before. > In > > general, you can use at most one index per table. So, for example, you > can't > > use both foo_z and the primary key index on foo, so it decides to use > the pk > > index because that's predicted to be more beneficial; statistics could > > easily push that the other way, though (for example, if there's only one > or > > two rows with c=10). The pk index on bar, on the other hand, is useless > for > > this query because we don't care what bar.a is; using bar_c index is the > > obvious choice there. > > > > I'm afraid I didn't follow your explanation of the grid and update stuff, > > but hopefully the above at least helps you make the query fast. > > > > > > Ryan > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
