Thanks for your help. The Explain Query plan says this: 0|4|TABLE SortOrder AS SP USING PRIMARY KEY ORDER BY 1|0|TABLE Table1 AS T1 WITH INDEX Table1_TheID 2|1|TABLE Table2 AS T2 WITH INDEX Table2_TheID 3|2|TABLE Table3 AS T3 WITH INDEX Table3_TheID 4|3|TABLE Table4 AS T4 WITH INDEX Table4_TheID
The analyze didn't seem to make any noticeable difference But I will have to retry when the server is not busy. The +asc in the order by is not being recognized as correct syntax. I am unfamiliar with using the + operator in this way - what does it do? Thanks again Richard -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 04 July 2006 09:04 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Performance query with many joins "Development" <[EMAIL PROTECTED]> wrote: > > This table gets filled in a random order with the SortID being generated = > on load. The Select query then changes slightly to enforce this order: > > SELECT > T1.TheID, > T1.Column1, > T1.Column6, > T2.Column2, > T2.Column3, > T2.Column6, > T3.Column8, > T4.Column2, > S.SortID > FROM > Table1 as T1 inner join Table2 as T2 on T1.TheID = T2.TheID > inner join Table3 as T3 on T1.TheID = T3.TheID > inner join Table4 as T4 on T1.TheID = T4.TheID > inner join SortOrder as S on T1.TheId = S.TheID > where > S.SortID >= 100001 and > S.SortID <= 150000 > order by > S.SortID asc > > This query performs very badly. What does EXPLAIN QUERY PLAN say? I'm guessing it might go faster if you (1) run ANALYZE first or (2) record the ORDER BY clause to say: "S.SortID +asc". -- D. Richard Hipp <[EMAIL PROTECTED]>
