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
