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.
