MySQL Forums
Forum List  »  Optimizer & Parser

Re: SQL syntax appears correct but EXPLAIN does not work?
Posted by: David Wynter
Date: July 18, 2005 08:17AM

Thanks, dooh.

Modified it to this:

SELECT * FROM SECM_SECURITY_UNIVERSE, PREFERRED_STOCK_INFORMATION, BOND_CORE_INFORMATION,
CONVERTIBLE_BOND_INFO
WHERE (SECM_SECURITY_UNIVERSE.ASSET_ID = PREFERRED_STOCK_INFORMATION.ASSET_ID AND
PREFERRED_STOCK_INFORMATION.PROCESSED>0)
OR (SECM_SECURITY_UNIVERSE.ASSET_ID = BOND_CORE_INFORMATION.ASSET_ID AND
BOND_CORE_INFORMATION.PROCESSED>0)
OR (SECM_SECURITY_UNIVERSE.ASSET_ID = CONVERTIBLE_BOND_INFO.ASSET_ID AND
CONVERTIBLE_BOND_INFO.PROCESSED>0

Explain returns this

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE PREFERRED_STOCK_INFORMATION ALL PRIMARY 3299
1 SIMPLE BOND_CORE_INFORMATION ALL PRIMARY 1100997
1 SIMPLE CONVERTIBLE_BOND_INFO ALL CONVERTIBLE_BOND_INFO_FK_1 18126 Using where
1 SIMPLE SECM_SECURITY_UNIVERSE ALL FK_SEC_UNIV_BOND_CORE_INFO 906533 Range checked for each record (index map: 0x8)

So it is no surprise the thing takes a day to run. What I do not get is why it does not use the indexes on the ASSET_ID column in each of the tables (they all have one)? Is it because of the PROCESSED column requiring a table scan? PPROCESSED is only ever 0 or 1

Thx.

David

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: SQL syntax appears correct but EXPLAIN does not work?
2551
July 18, 2005 08:17AM


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.