On Tuesday, 1 October, 2019 11:58, Fredrik Larsen <frel...@gmail.com> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users