MySQL Forums
Forum List  »  Optimizer & Parser

Re: SQL syntax appears correct but EXPLAIN does not work?
Posted by: Jay Pipes
Date: July 18, 2005 09:04AM

No, it's because you're using OR conditions, which pre 5.0 circumvent the optimizer. Use UNION to bypass this shortcoming:

SELECT * FROM SECM_SECURITY_UNIVERSE ssu
INNER JOIN PREFERRED_STOCK_INFORMATION psu
ON ssu.ASSET_ID = psuy.ASSET_ID
WHERE psu.PROCESSED > 0
UNION
SELECT * FROM SECM_SECURITY_UNIVERSE ssu
INNER JOIN BOND_CORE_INFORMATION bcu
ON ssu.ASSET_ID = bcu.ASSET_ID
WHERE bci.PROCESSED>0
UNION
SELECT * FROM SECM_SECURITY_UNIVERSE ssu
INNER JOIN CONVERTIBLE_BOND_INFO cbi
ON ssu.ASSET_ID = cbi.ASSET_ID
WHERE cbi.PROCESSED > 0;

This should allow the indexes on ASSET_ID to use on eq_ref access strategy. BTW, putting an index on PROCESSED column is a waste since it has such low cardinality.

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: SQL syntax appears correct but EXPLAIN does not work?
2548
July 18, 2005 09:04AM


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.