Yes!! SQLITE_ENABLE_STAT2 did the trick. I can see that the SQLite library increased some 10 kB after this change. No big deal.
Why isn't this flag on by default? > From: kristoffer.daniels...@live.se > To: sqlite-users@sqlite.org > Date: Sun, 1 Nov 2009 17:04:41 +0100 > Subject: Re: [sqlite] How to decide which table is the outer table and which > table is the inner table? > > > 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 _________________________________________________________________ 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
