Yes, your note about DESC ordering is the only thing missing now, and then everything will be perfect :) I have a build of sqlite with the GROUP-BY-DESC patch applied, but not able to test now. But previous testing using very similar queries did work fine so don't expect any problems.
Now all my queries are below 10ms on fairly big data-sets, from 1-2 seconds. That is an improvement of several orders of magnitude :) Fredrik On Tue, Oct 1, 2019 at 8:49 PM Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
