The parentheses around A is a workaround to avoid the "natural self-join" bug (fixed in 3.6.20+ as far as I know). In this particular case they can be omitted, with no difference. The parenthesis around B+C+D are there to get a valid inner join - B and D do not contain A_ID, but C does.
These are ultra-fast: SELECT COUNT(A_ID) FROM A INNER JOIN (B NATURAL JOIN C NATURAL JOIN D) USING (A_ID); SELECT COUNT(A_ID) FROM B NATURAL JOIN C NATURAL JOIN D NATURAL JOIN A; These are ultra-slow: SELECT COUNT(A_ID) FROM A NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D); SELECT COUNT(A.A_ID) FROM (B NATURAL JOIN C NATURAL JOIN D) NATURAL JOIN A; I'm wondering if the optimizer makes a stupid choice due to the SQLITE_ENABLE_STAT2 flag, which was not set during compilation. I'll try the ANALYZE command after recompiling with this flag activated, and see if it makes any difference. And if it doesn't, I'll make sure to produce a sample that illustrates the problem. Thanks. > From: paiva...@gmail.com > Date: Sun, 1 Nov 2009 10:33:58 -0400 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] How to decide which table is the outer table and which > table is the inner table? > > > (B + C + D) have two columns that are present in A, of which only one is > > indexed. Could that be the culprit? > > There's an easy way to check that: just re-write your query so that it > contains joins with explicitly pointed condition which columns join > should be made on. > > BTW, why are you putting parenthesis there? Maybe it makes less > freedom to choose for SQLite's optimizer? > > Pavel > > On Sat, Oct 31, 2009 at 7:27 PM, Kristoffer Danielsson > <kristoffer.daniels...@live.se> wrote: > > > > All I know is that SQLite chooses the wrong order when I give it my query > > at this form: > > > > > > > > SELECT COUNT(A_ID) FROM (A) NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D); > > > > > > > > Where: > > > > A = 50,000 rows. > > > > B = 2 rows. > > > > C = 10,000 rows > > > > D = 250,000 rows > > > > > > > > > > > > (B + C + D) have two columns that are present in A, of which only one is > > indexed. Could that be the culprit? > > > > > > > > Though, if I put A last, the query is blistering fast. Clearly, SQLite does > > something wrong in this case. > > > > > > > > > > > > > > > >> From: paiva...@gmail.com > >> Date: Fri, 30 Oct 2009 12:16:17 -0400 > >> To: sqlite-users@sqlite.org > >> Subject: Re: [sqlite] How to decide which table is the outer table and > >> which table is the inner table? > >> > >> Of course SQLite wasn't changed much in this part since November 2008 > >> but the citation you gave is either wrong or the key words in it are > >> "something like" in phrase "SQlite does something like this". Because > >> SQLite is smart enough to choose smaller table as an outer one and > >> bigger table as an inner one. Although it can choose other way round > >> if you have index on Id in smaller table and don't have index on Id in > >> bigger table. And in this case there's no performance hit in such > >> decision, only benefit. > >> > >> Pavel > >> > >> On Fri, Oct 30, 2009 at 12:07 PM, Kristoffer Danielsson > >> <kristoffer.daniels...@live.se> wrote: > >> > > >> > Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx > >> > > >> > > >> > > >> > SQLite uses only nested loops to implement joins. Given a query like the > >> > following: > >> > > >> > SELECT ... > >> > FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id > >> > > >> > > >> > > >> > SQlite does something like this: > >> > > >> > > >> > > >> > for each row in OuterTable > >> > Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id > >> > end for each > >> > > >> > > >> > > >> > I assume SQLite has not improved on JOIN precedence since then, which > >> > means that if OuterTable is HUGE, there will be an huge performance hit! > >> > > >> > > >> > > >> > "Some database engines like SQL Server decide which table is the outer > >> > table and which table is the inner table" > >> > > >> > > >> > How do I simulate that behavior in SQLite? A misformed SQL statement > >> > from the user results in unacceptable lockups... > >> > > >> > _________________________________________________________________ > >> > Nya Windows 7 - Hitta en dator som passar dig! Mer information. > >> > http://windows.microsoft.com/shop > >> > _______________________________________________ > >> > sqlite-users mailing list > >> > sqlite-users@sqlite.org > >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > _________________________________________________________________ > > Nya Windows 7 gör allt lite enklare. Hitta en dator som passar dig! > > http://windows.microsoft.com/shop > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _________________________________________________________________ Hitta kärleken nu i vår! http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
