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]
