On Sat, Nov 30, 2013 at 5:24 AM, George <pinkisntw...@gmail.com> wrote:
> I have a query that is used to populate a table in my website. The query > joins 4 different tables and returns around 10 columns, and I want to order > on 4 of them, each of those 4 being on a different table. I also use a > LIMIT clause for pagination. > > 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. > > Is there some way to avoid this TEMP B-TREE? Since I am using LIMIT 25, the > database really only needs to order those 25 (or slightly more) rows. > If you have a LIMIT 25, then the TEMP B-TREE never holds more than 25 rows, specifically the top 25 rows seen so far. But SQLite still has to scan through the entire results set looking for other rows that belong in the top 25. If you only order by the first column, then apparently there are indices that can be used to cause the results to come out of the query in the correct order to begin with, so only the first 25 rows of the result set need to be examined. That's normally much, much faster than scanning the entire result setting looking for the 25 best. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
