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 <[email protected]>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
