Simen; ANALYZE and PRAGMA reverse_unordered_selects = YES does not affect results.
Hick; ORDER BY x DESC >is< covered by index. Btree-indexes allows traversal both ways. You can see this if you remove GROUP_BY. Got an answer on StackOverflow that seems to be from somebody that knows internal details of sqlite. Depressing if this is true as this optimization seems trivial compared to other optimizations implemented in sqlite and it effectively stops my sqlite-project Fredrik On Thu, Sep 19, 2019 at 5:13 PM Hick Gunter <h...@scigames.at> wrote: > An ORDER BY clause will omit sorting only if the visitation order exactly > fulfills the clause. > > A GROUP BY clause is able to avoid creating a temporary table if the > visitation order exactly fulfills the clause. > > If a SELECT references only fields present in an index, that (covering) > index may be used instead of the table. > > In your case, the SELECT references fields x and y, both of which are > present in the index, so the QP uses the covering index. This also happens > to be sorted by x, allowing the GROUP BY to avoid a temporary table and > producing rows odered by x. > > The ORDER BY x in the first query is thus already fulfilled and so no > sorting is required. > > The ORDER BY x DESC in the second query is thus NOT fulfilled, so a > sorting step is required. > > Unfortunately, trying to be clever by creating an index on (x desc, y) > does not help. > > -----Ursprüngliche Nachricht----- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Fredrik Larsen > Gesendet: Donnerstag, 19. September 2019 14:14 > An: sqlite-users@mailinglists.sqlite.org > Betreff: [EXTERNAL] [sqlite] Group-by and order-by-desc does not work as > expected > > I have a aggregate query that works as expected when the ordering is > ascending, but uses a TMP B-TREE when changing order to descending, see > stackoverflow link below. > > Is there something I'm missing? I would expect same performance when > ordering both directions. > > Link: > > https://stackoverflow.com/questions/58009898/sqlite-group-by-with-sort-by-desc-does-not-work-as-expected > > > Fredrik Larsen > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick | Software Engineer | Scientific Games International GmbH | > Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) > +43 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > _______________________________________________ > 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
