You mean something like this:
select key,
maxrev,
data
from (
select key,
null as maxrev,
null as data
from t1
where key not in (select key
from t2
where rev < :rev)
union all
select t1.key,
max(rev) as maxrev,
data
from t1, t2
where t1.key == t2.key
and rev < :rev
group by t2.key
)
order by key;
t1 should have an index on key
t2 should have an index on key, rev
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Fredrik Larsen
>Sent: Monday, 30 September, 2019 04:12
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: [sqlite] Lookup join
>
>Consider query below;
>
>SELECT key
>FROM t1
>LEFT JOIN (
> SELECT key,max(rev),data
> FROM t2
> WHERE rev < ?
> GROUP BY key
>) USING (key)
>ORDER BY key ?
>LIMIT ?
>
>In above query sqlite will materialize the t2-sub-query and then start
>working on the outer query. I have a lot of data in t2 so this will
>consume
>a lot of time.
>
>To overcome this I perform above query manually i two stages; I fetch the
>t1 data, then for each row I do a lookup and manually join. This is very
>fast by but makes it hard to reuse this base-query in other queries.
>
>So my question is; Can sqlite do lookup-type joins, like to do manually
>in
>code, to avoid the overhead of materializing the full t2-query on all
>keys,
>and using just a fraction of this work?
>
>I suspect the answer is no, if so, maybe this is solvable through a
>custom
>virtual table? I have looked at ext/misc/eval.c, and this custom function
>could be used if a function where alloed to return multiple columns..
>
>Fredrik
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
