I´m not sure why you think group_by + order_by_desc + limit N queries are so obscure? Useful for lots of tail-statistics (number of transactions last N hours if group_key is time-based, etc).
In my case I'm implementing a event-store using sqlite, where I need to be able to retrieve entity data at specific revision, ordered by id to allow top/bottom type queries. SELECT max(rev),* FROM db WHERE rev <=? GROUP BY id ORDER BY id ? LIMIT ?; -- Very slow if ordering happens to be DESC :( Yes, above query depend on a quirk of sqlite (bare data?) Fredrik On Fri, Sep 20, 2019 at 3:50 PM R Smith <ryansmit...@gmail.com> wrote: > On 2019/09/20 2:49 PM, Fredrik Larsen wrote: > > Hi Ryan > > > > Nobody is proposing that QP should automagically add an index, I'm only > > asking why the QP does not use already added index, that is specially > added > > for this specific case. I don't thinks this is a very "obscurest of > > use-case" or to much to ask for, in fact, this is the expected behavior > for > > even the simplest SQL engines, and so especially sqlite. > > Apologies if I was unclear, I'm not shouting at you for asking - this is > not that kind of forum, and it's a perfectly good request. > > To be clear, I did not mean you are asking for it to index, I am saying > that it already (automagically) makes an index to use for itself in > service of the group-by, which is grand because it also solves the > forward ordering without the need of an explicit index. > > I further tried to make the case that It only makes this index as a > temporary index that cannot/will not currently be traversed in the > reverse, which is where it falls short to your specific (yes, very > obscure) use case. > > However, my point was forged more towards you not depending on the Index > it makes for anything, upon reading DD's post, but after re-reading your > post, I see the question is not so much that you expect the behaviour to > get a certain output, but that the output comes at the cost of speed > where it should not carry any speed penalty. > > In this regard, let me adjust my original statement more in the > direction of ambivalence. The optimization should be implemented if > easy. The use case is obscure, but definitely valid, and more > importantly, I don't think there is another way to fix it. If, on the > other hand, it is CPU heavy in some way, or IO heavy for very large > selects, avoid it rather. I doubt more than 0.00001% of all goup-by > output is DESC ordered[1]. > > > Cheers, > Ryan > > [1] - That is a completely made up statistic (obviously) and I will > happily abide by a higher figure if claimed, but that burden of proof is > not on me. :) > > > _______________________________________________ > 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
