MySQL Forums
Forum List  »  Optimizer & Parser

Re: optimization of left outer joins
Posted by: Jay Pipes
Date: December 16, 2005 10:23AM

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?

Options: ReplyQuote

Written By
December 16, 2005 09:25AM
Re: optimization of left outer joins
December 16, 2005 10:23AM
December 19, 2005 11:42PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.