> (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
