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
