Nice one Keith. Thanks. sqlite> create table t1 (a, b text, c); sqlite> create table t2 (d, e text, f); sqlite> create index b on t1(b); sqlite> create index e on t2(e); sqlite> explain query plan select * from t1,t2 order by b, t1.RowID, e, t2.RowID; 0|0|0|SCAN TABLE t1 USING INDEX b 0|1|1|SCAN TABLE t2 USING INDEX e
although I still don’t really understand why a TEMP B-TREE would be quicker than using index e for this sqlite> explain query plan select * from t1,t2 order by b, e; 0|0|0|SCAN TABLE t1 USING INDEX b 0|1|1|SCAN TABLE t2 0|0|0|USE TEMP B-TREE FOR RIGHT PART OF ORDER BY After all, it’s attaching an entire table to each row in t1 in an order that’s already been worked out. From: Keith Medcalf<mailto:kmedc...@dessus.com> Sent: 24 March 2018 20:34 To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Determine sort order of query Hint: Index entries must be unique. They are made unique by having the rowid in the index (how else would you find the row from the index)? if you asked for the data in an order that can be produced by an index without a sort, then you will get the output without a sort. select * from t1, t2 order by b, t1.rowid, d, t2.rowid; Index b can be used to get the initial column sorted, but a separate sorter is required to get the order you asked for, for each subgroup (next bunch of columns). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of x >Sent: Saturday, 24 March, 2018 13:08 >To: SQLite mailing list >Subject: Re: [sqlite] Determine sort order of query > >sqlite> create table t1(a, b text); >sqlite> create table t2(c, d text); >sqlite> create index b on t1(b); >sqlite> create index d on t2(d); >sqlite> explain query plan select * from t1,t2 order by b, d, >t1.RowID, t2.RowID; >0|0|0|SCAN TABLE t1 USING INDEX b >0|1|1|SCAN TABLE t2 >0|0|0|USE TEMP B-TREE FOR RIGHT PART OF ORDER BY > >Is there a reason it uses TEMP B-TREE rather than index t2(d) ? > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
