In my query table1 and table2 have about ~700000 records, table3 ~500000 records. The result set is ~10000 records. It sems to me that the bigger the tables the bigger performance difference.
--- [EMAIL PROTECTED] wrote: > I am curious how many rows are in each of your test > tables. Have you tried > this with a few million rows in each table? > > Alexei Novakov <[EMAIL PROTECTED]> > 03/10/2004 01:22 PM > > > To: [EMAIL PROTECTED] > cc: (bcc: Josh Ralph/IS/Expeditors) > Subject: Outer join performance hint. > > > Hi All, > > I don't really know DB which is doing fine with > optimization of outer joins. MaxDB is not an > exception. I think there is room for optimization > though. Let's look at the following example: > > 1) > select t1.col1, t2.col1, t3.val > from table1 t1, table2.t2 > left outer join table3 t3 > on t3.col1 = t1.col1 and t3.col2 = t2.col1 > where ... > > This is not just slow, but it also uses a hard drive > very extencively. Query 1 can be substituted in most > of cases with the following: > > 2) > select t1.col1, t2.col1, > (select val from table3 > where col1 = t1.col1 > and col2 = t2.col1) as val > from table1 t1, table2 t2 > where ... > > This query looks scarier, but it works 3-5 times > faster and doesn't cause disk acivity as the first > one. > > Generally speaking, query 1 for vast majority of > queries can be converted into form 2. On the other > hand query 1 provides much more ground for > optimization to the SQL processor. > > Are there any plans to work on outer join > optimization? > > Best regards. > > Alexei. > > > __________________________________ > Do you Yahoo!? > Yahoo! Search - Find what you're looking for faster > http://search.yahoo.com > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > __________________________________ Do you Yahoo!? Yahoo! Search - Find what you�re looking for faster http://search.yahoo.com -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
