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.
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
