In the following scenario:
CREATE TABLE t1 (id INTEGER, name text, UNIQUE (name));
CREATE TABLE t1c (id INTEGER, name text, t1_id INTEGER, UNIQUE (name));
CREATE INDEX idx_t1c_by_t1_id ON t1c (t1_id, id);
CREATE TABLE t2 (id INTEGER, name text, UNIQUE(name));
CREATE TABLE t2c (id INTEGER, name text, t2_id INTEGER, UNIQUE
(t2_id, name));
I have a query designed to generate a row for t2c ordered by t2 for
every instance of t1c ordered by t1 id and then t1c id.
The query uses indexes and those should ensure that the results are in
the order I am specifying:
EXPLAIN QUERY PLAN
SELECT t1c.t1_id, t1c.id, t2c.t2_id, t2c.id
FROM t1c,
t2 INNER JOIN t2c ON (t2c.t2_id = t2.id)
ORDER BY t1c.t1_id, t1c.id, t2.name, t2c.name
;
And yet the plan invokes a B-Tree to sort:
"0" "0" "0" "SCAN TABLE t1c USING COVERING INDEX
idx_t1c_by_t1_id"
"0" "1" "2" "SCAN TABLE t2c"
"0" "2" "1" "SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX
(id=?)"
"0" "0" "0" "USE TEMP B-TREE FOR RIGHT PART OF ORDER BY"
Is the temp b-tree redundant here?
$ sqlite3 --version
3.8.5 2014-06-04 14:06:34 b1ed4f2a34ba66c29b130f8d13e9092758019212
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
