Hi,
i've a question about how to control the order in
which sqlite reads tables. here is the basic setup:
table foo (f_id integer primary key, foo text, prio
integer);
table bar (b_id integer primary key, bar text, prio
integer);
table foobar (fb_id integer primary key, f_id integer,
b_id integer, result blob);
the tables foo and bar provide parameters for
calculations which are stored in foobar. all possible
combinations of foo and bar shall be computed. However
some values of foo and some values of bar are more
important than others; the prio field controls that.
A basic query to get a chunk of combinations of foo
and bar which still need to be computed would look
like:
select foo.f_id, foo.foo, bar.b_id, bar.bar from foo
join bar left join foobar on foobar.f_id = foo.f_id
and foobar.b_id = bar.b_id where foobar.fb_id is null
limit 100;
sqlite will read the tables foo and bar without any
specific order. I have two options how to use the prio
values:
- select ... is null order by foo.prio, bar.prio limit
100;
- select ... from (select distinct * from foo order by
prio) as f2 join (select distinct * from bar order by
prio) as b2 ... limit 100;
For my use, sqlite "overfulfills" the order statement.
Every time the query is executed, sqlite will read
both tables completely, join them, order the result
and *then* check against the foobar table (explain
query plan reveals that). As foo and bar each contain
tens of thousands and foobar millions of values, this
query takes an awfully long time. A statement like
select ... where foo.prio = (select max(prio) from
foo) ...
is done in a few milliseconds, however the result is
not exactly the same - if foobar contains all values
for that priority, the resultset would be empty.
Does anyone know how to describe the kind of problem -
having 2 tables to be read in a specific order, both
their values getting checked against another and the
complete cross join not being computed in advance
every single time?
Heute schon einen Blick in die Zukunft von E-Mails wagen? Versuchen Sie´s
mit dem neuen Yahoo! Mail. www.yahoo.de/mail
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
