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
