On Sun, 16 Sep 2018 19:27:40 +1000 Barry Smith <smith.bar...@gmail.com> wrote:
> Without an order by, sqlite can return the rows in any order it pleases. > Likely whatever consumes the least resources. Although unlikely given your > indices, it might be possible - for instance if some future > micro-optimisation finds that it's quicker to read the index in reverse, then > sqlite would give things in the opposite order. If you leave out a necessary > order by you are very much exposing yourself to internal changes. So much so > that there is a pragma reverse_unordered_selects > (https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects) you can > use to specifically find if your app makes invalid assumptions about order. Well, I was sure that the first answer will be this. But I asked the question in hope to get some deeper information about the relations between order by, group by and the indices used. It is not a coincidence that in the example case I used "INDEXED BY" clause. With PRAGMA reverse_unordered_selects=1; all the queries in the example return the rows in exactly the same proper order. I tryed to insert the values in random order in order to avoid the primary key influence on the ordering and the result is still the same. IMO, this somehow proves that I can use such implicit ordering in this very case. Or not? > > You might have found a missed optimisation opportunity here (although there's > always the tradeoff of library size & cycles to optimise vs execution cycles > saved to consider). > > > On 16 Sep 2018, at 6:29 pm, John Found <johnfo...@asm32.info> wrote: > > > > Is there some relation between the indexes used in the query, the GROUP BY > > fields used > > and the order of the result rows, when no "ORDER BY" clause is used? > > > > I am asking, because I noticed, that on some queries, when I am using > > "ORDER BY" the query always > > use temporary b-tree for ordering, but by including the needed fields in > > the "GROUP BY" clause > > and removing the ORDER BY clause, the query returns the rows in the proper > > order without temp b-tree. > > > > So, is it safe to use this implicit ordering, or this behavior can be > > changed in the future versions of SQLite? > > > > Here is an example: > > > > create table A ( > > id integer primary key autoincrement, > > o1 integer, > > o2 integer > > ); > > > > create table B ( > > Aid integer references A(id), > > data text > > ); > > > > create index idxA on A(o1 desc, o2 desc); > > > > insert into A(o1, o2) values (1, 100), (2, 50), (3, 200), (5, 300); > > insert into B(Aid, data) values (1, "b"), (1, "a"), (2, "c"), (2, "d"), (2, > > "e"), (3, "f"), (3, "g"); > > > > -- Always uses temp b-tree for order by > > select > > group_concat(B.data), o1, o2 > > from > > A > > left join > > B on A.id = B.Aid > > group by > > A.id > > order by > > A.o1 desc, A.o2 desc; > > > > explain query plan: > > id parent notused detail > > 8 0 0 SCAN TABLE A > > 19 0 0 SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?) > > 58 0 0 USE TEMP B-TREE FOR ORDER BY > > > > > > -- This one returns the rows in the needed order without ORDER BY > > select > > group_concat(B.data), o1, o2 > > from > > A indexed by idxA > > left join B on A.id = B.Aid > > group by A.id, A.o1, A.o2; > > > > explain query plan: > > id parent notused detail > > 7 0 0 SCAN TABLE A USING COVERING INDEX idxA > > 18 0 0 SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?) > > > > > > -- But if I add ORDER BY it still begins to use temp b-tree > > -- regardless that it does not change the order. > > select > > group_concat(B.data), o1, o2 > > from > > A indexed by idxA > > left join B on A.id = B.Aid > > group by A.id, A.o1, A.o2 > > order by A.o1 desc, A.o2 desc; > > > > explain query plan: > > 8 0 0 SCAN TABLE A > > 19 0 0 SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?) > > 60 0 0 USE TEMP B-TREE FOR ORDER BY > > > > > > All the above queries, returns the same result rows in the same order: > > > > group_concat(B.data) o1 o2 > > NULL 5 300 > > f,g 3 200 > > c,d,e 2 50 > > a,b 1 100 > > > > > > > > > > -- > > John Found <johnfo...@asm32.info> -- John Found <johnfo...@asm32.info> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
