On Tuesday, 1 October, 2019 11:58, Fredrik Larsen <[email protected]> wrote:
>Thanks Keith! I have spent several days trying to tune my query towards
>expected performance, without luck. I somehow missed your fairly straight
>forward solution. I still have some problems making sqlite use the
>correct indexes, but this can at least be fixed by well-placed
>INDEXED-BY-hints.
select t1.key,
max(t2.rev) as maxrev,
t2.data
from t1
left join t2
on t1.key == t2.key
and t2.rev < ?
group by t1.key
order by t1.key (asc|desc)
limit ?
;
I should think that this version would give the query planner the widest
latitude to use the available indexes (again, on t1 (key) and t2 (key, rev)).
With the upcoming 3.30.0 version of SQLite3 you should get the same performance
when using "order by t1.key desc" as when using "order by t1.key asc" as it
will push the order down into the (group by) rather than using an extra sorter
... and it should only require to traverse t1 in index order and do one index
lookup per t1 row into t2 -- so theoretically it should be the absolute minimum
work required to answer the query.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
