Any thoughts? -----Original Message----- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Monday, June 18, 2007 10:10 AM To: mysql@lists.mysql.com Subject: INNER versus OUTER
I'm using the latest MySQL with InnoDB and something is happening I don't understand. I am going to try this first by paraphrasing my queries since they are complex and have some proprietary info in them. It seems that when a LEFT OUTER or an INNER join will produce the same result and other joins in the query are LEFT OUTER that I get a better query plan if I make the JOIN that could go either way a LEFT OUTER instead of an INNER. For example consider the following query (everything is indexed)... SELECT <Field_List> FROM Header JOIN ON HeaderNames ON Header.nameID = HeaderNames.ID LEFT OUTER JOIN Items LastItem ON Header.lastItemID = LastItem.ID WHERE Header.nodeID = 20 ORDER BY Header.ruleID; This seems to run much slower than the following: SELECT <Field_List> FROM Header LEFT OUTER JOIN ON HeaderNames ON Header.nameID = HeaderNames.ID LEFT OUTER JOIN Items LastItem ON Header.lastItemID = LastItem.ID WHERE Header.nodeID = 20 ORDER BY Header.ruleID; Looking at the query plan, the first query puts the HeaderNames table first and Uses a temporary and a filesort. The second query examines a few more rows but it puts the Header file first and uses a Where and index for everything. Could someone explain to me why this is? Does it have something to do with the ORDER BY? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]