It isn't. In fact if you do a SELECT b, e FROM t1, t2 ORDER BY b, e;
you get both the covering indexes being used and a temp sorter (although the temp sorter does nothing). Perhaps an opportunity for future optimization? --- 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- >[email protected]] On Behalf Of x >Sent: Sunday, 25 March, 2018 02:40 >To: SQLite mailing list >Subject: Re: [sqlite] Determine sort order of query > >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:[email protected]> >Sent: 24 March 2018 20:34 >To: SQLite mailing list<mailto:[email protected]> >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- >>[email protected]] 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 >>[email protected] >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > >_______________________________________________ >sqlite-users mailing list >[email protected] >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >_______________________________________________ >sqlite-users mailing list >[email protected] >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
