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) > > > 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.
So basically what you said is: "Don't make a foreign key. Instead create an index on the foreign key field and it will speed things up" Am I right? What about making both foreign key and index? Will this improve it even further? Thank you. > > > 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
