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
