Clive Cox wrote:
> So, why can't mysql optimize the "left outer join"
> version the same way using the primary keys so it
> doesn't need to trawl through all MemberState rows
> to get the <> 5 condition?
This actually has nothing to do with the <> 5 condition (more on the <> condition below). The optimizer may have a different threshold of rows to compare when performing the LEFT JOIN, and so therefore decides that it will be quicker to do a table scan than doing referential lookups via the primary key values like it does in the INNER JOIN example. For the LEFT JOIN, because it knows that it already must bring in all the values from the left side of the equation, it may decide that a table scan is cheaper...
> Is there something fundamental as to why the left
> outer join version can't be optimized this way?
In fact, it *is* optimized, the problem is the <> 5 condition...
> If there is a fundamental problem, then I can go
> back and complain more vigourously to the JDO
> vendor and ask how I can ensure JDO produces
> non-left outer join for my query!
There is a fundamental difference between a LEFT JOIN and an INNER JOIN. If you have asked the JDO to produce a LEFT JOIN, when in fact you could do with an INNER JOIN, perhaps there is a setting you need to change in the JDO code (not sure, I'm not a Java expert...). I'm pretty sure that the default behaviour of the JDO is *not* to simply use a LEFT OUTER JOIN unless there is a reason for doing so (perhaps because the schema does not have adequate referential integrity -- just a guess... again, not sure on that one).
As for the poor response of the query, unfortunately, much of that probably is caused by the <> 5 condition. Any time you have a <> condition, you elmiinate the use of any index on that particular field.
HTH a bit,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com