Ryan, On Thu, Mar 7, 2013 at 5:26 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: > Well, you *do* want an index for the target side of the join, but for FK > joins the existing PK index already takes care of that.
OK, > > At this point you only need to add an index on d.id to make the d.id=1 test > fast. And yes, it will make updates predicated on d.id faster as well. Well, I just checked and the update is on both id and playerid. Do I need the index on both fields? There is also an insert/delete into/from this table but it's done very rare so I don't care about that. Thank you. > > Ryan > > > On 07/03/2013 8:04 AM, Stephen Chrzanowski wrote: >> >> 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 > > > _______________________________________________ > 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
