Oliver Smith wrote: >> On Sun, Nov 16, 2014 at 2:18 PM, Oliver Smith<oli...@kfs.org> wrote: >>> ... >>> CREATE TABLE t2c (id INTEGER, name text, t2_id INTEGER, UNIQUE (t2_id, >>> name)); >>> >>> 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? >> >> I don't think so. What query plan are you thinking might be able to omit >> the sorting pass in this query? > > The t2c table has an index on id, name; I expected it would use that > index so that the data would be naturally in order.
If you had used "ORDER BY t2c.id, t2c.name, ...", it would be possible to use this index. How would a different order help? Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
