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