Re: [sqlite] Inefficient query plan in queries with multiple joins

2013-11-30 Thread George
On Sat, Nov 30, 2013 at 1:55 PM, Richard Hipp wrote: > > 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

Re: [sqlite] Inefficient query plan in queries with multiple joins

2013-11-30 Thread Jim Callahan
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: > S

Re: [sqlite] Inefficient query plan in queries with multiple joins

2013-11-30 Thread Richard Hipp
On Sat, Nov 30, 2013 at 7:08 AM, Simon Slavin wrote: > > On 30 Nov 2013, at 10:24am, George 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

Re: [sqlite] Inefficient query plan in queries with multiple joins

2013-11-30 Thread Simon Slavin
On 30 Nov 2013, at 10:24am, George 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

Re: [sqlite] Inefficient query plan in queries with multiple joins

2013-11-30 Thread Richard Hipp
On Sat, Nov 30, 2013 at 5:24 AM, George 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 pagin

Re: [sqlite] Inefficient query plan in queries with multiple joins

2013-11-30 Thread Kees Nuyt
On Sat, 30 Nov 2013 12:24:01 +0200, George 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 pagina

[sqlite] Inefficient query plan in queries with multiple joins

2013-11-30 Thread George
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 c