Would the rtree be useful in a composite primary key and/or ad-hoc composite query context? http://www.sqlite.org/rtree.html
If so, how would SQLite know to create and/or use an rtree without explicit user intervention? Jim Callahan > But this does point out a possible enhancement to SQLite: > Suppose the query is something like this: > SELECT a,b,c,d,e,f FROM ... WHERE ... ORDER BY a,b,c,d LIMIT 25; > If SQLite is unable to find a combination of indices that makes things > naturally come out in a,b,c,d order, then it scans the entire result set > looking for the 25 entries with the least values for a,b,c,d. Even if the > indices guarantee that "a" is coming out in order, because b,c,d are not in > order, it still scans the entire result set. > Suppose "a" is in order but b,c,d are not. Then if the TEMP B-TREE is > filled up with 25 entries and if the current "a" is greater than the > largest "a" in the TEMP B-TREE, the scan can stop at that point, no? But > SQLite keeps on scanning until the end, even though none of the extra rows > scanned will ever be in the top 25. > Version 3.8.2 is in its quiet period right now. But maybe we can do > something about this for version 3.8.3..... On Sat, Nov 30, 2013 at 7:26 AM, Richard Hipp <d...@sqlite.org> wrote: > On Sat, Nov 30, 2013 at 7:08 AM, Simon Slavin <slav...@bigfraud.org> > wrote: > > > > > On 30 Nov 2013, at 10:24am, George <pinkisntw...@gmail.com> wrote: > > > > > I have noticed that when I order using just one column then the query > is > > > very fast, because no TEMP B-TREE is used. When I add the other columns > > > then TEMP B-TREE is used and the query is very slow. > > > > Do you have indexes on those four tables ideally suited to the query > > you're executing ? Think about how you, as a human, would need to look > > things up on each table to satisfy your JOIN and ORDER BY clauses. > > > > > For joins, it isn't sufficient just to have indices on the order-by > columns. There are lots of other conditions as well. Generally speaking, > the indices need to be UNIQUE and the columns indexed need to be NOT NULL. > The OP's query might not be amenable to that. > > But this does point out a possible enhancement to SQLite: > > Suppose the query is something like this: > > SELECT a,b,c,d,e,f FROM ... WHERE ... ORDER BY a,b,c,d LIMIT 25; > > If SQLite is unable to find a combination of indices that makes things > naturally come out in a,b,c,d order, then it scans the entire result set > looking for the 25 entries with the least values for a,b,c,d. Even if the > indices guarantee that "a" is coming out in order, because b,c,d are not in > order, it still scans the entire result set. > > Suppose "a" is in order but b,c,d are not. Then if the TEMP B-TREE is > filled up with 25 entries and if the current "a" is greater than the > largest "a" in the TEMP B-TREE, the scan can stop at that point, no? But > SQLite keeps on scanning until the end, even though none of the extra rows > scanned will ever be in the top 25. > > Version 3.8.2 is in its quiet period right now. But maybe we can do > something about this for version 3.8.3..... > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > 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
