If you do ORDER BY t1c.t1_id, t1c.id; then you won't have the b-tree step, but including the name fields means it has the extra work to do to satisfy your order by. Or am I missing something?
John On Sun, Nov 16, 2014 at 1:18 PM, Oliver Smith <oli...@kfs.org> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
