Query ran for 9 days!

[David Wynter]

Sorry about the long query, it has a complex projection due to the need to produce a complex message based on an ISO standard.

I had run this query in a series of queries in a application then went on holiday, when I came back it had got to this query and continued to run for 9 days before I then killed the thread.

I found the cause, there where 1.5M spurious event in the corax_event table. However this did give me a taste of what the table will be like in a years time. So I have implemented the index you suggested. It is still not used as the optimiser chooses a better path now that I have deleted the spurious event records. I assume it will change approach once there are 1 million (or whatever is optimal) event records in corax_event. So thanks the pointer on the 2 column index, I am starting to 'get' it now about how the indexes work. The current explain plan.

*************************** 1. row ***********************
id: 1
select_type: PRIMARY
table: cp
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using temporary
*************************** 2. row ***********************
id: 1
select_type: PRIMARY
table: cw
type: ref
possible_keys: PRIMARY,FK_customer_watch_list_2
key: PRIMARY
key_len: 4
ref: securitymaster.cp.Portfolio_Id
rows: 27190
Extra: Using index
*************************** 3. row ***********************
id: 1
select_type: PRIMARY
table: ev
type: ref
possible_keys: Asset_Id_idx,Event_Type_idx,Security_Id_idx
key: Security_Id_idx
key_len: 9
ref: securitymaster.cw.SecurityId
rows: 1
Extra: Using where
*************************** 4. row ***********************
id: 1
select_type: PRIMARY
table: bc
type: eq_ref
possible_keys: PRIMARY,FK_BOND_CORE_INFO_FI_ISSUER_INFO
key: PRIMARY
key_len: 8
ref: securitymaster.ev.ASSET_ID
rows: 1
Extra: Using where
*************************** 5. row ***********************
id: 1
select_type: PRIMARY
table: ii
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: securitymaster.bc.ISSUER_ID
rows: 1
Extra:
*************************** 6. row ***********************
id: 1
select_type: PRIMARY
table: x1
type: ref
possible_keys: IX_ID_EFF_DATE
key: IX_ID_EFF_DATE
key_len: 11
ref: securitymaster.ev.ASSET_ID,const
rows: 1
Extra:
*************************** 7. row ***********************
id: 1
select_type: PRIMARY
table: x2
type: ref
possible_keys: IX_ID_EFF_DATE
key: IX_ID_EFF_DATE
key_len: 11
ref: securitymaster.ev.ASSET_ID,const
rows: 1
Extra:
*************************** 8. row ***********************
id: 1
select_type: PRIMARY
table: x3
type: ref
possible_keys: IX_ID_EFF_DATE
key: IX_ID_EFF_DATE
key_len: 11
ref: securitymaster.ev.ASSET_ID,const
rows: 1
Extra:
*************************** 9. row ***********************
id: 1
select_type: PRIMARY
table: x4
type: ref
possible_keys: IX_ID_EFF_DATE
key: IX_ID_EFF_DATE
key_len: 11
ref: securitymaster.ev.ASSET_ID,const
rows: 1
Extra:
*************************** 10. row **********************
id: 1
select_type: PRIMARY
table: ci
type: ref
possible_keys: COUPON_INFORMATION_FK_1
key: COUPON_INFORMATION_FK_1
key_len: 8
ref: securitymaster.bc.ASSET_ID
rows: 1
Extra:
*************************** 11. row **********************
id: 1
select_type: PRIMARY
table: mm
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 1
ref: securitymaster.ci.DAY_COUNT_CD
rows: 1
Extra:
*************************** 12. row **********************
id: 1
select_type: PRIMARY
table: ex
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: securitymaster.bc.ASSET_ID
rows: 1
Extra: Using index
*************************** 13. row **********************
id: 1
select_type: PRIMARY
table: bi
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: securitymaster.bc.ASSET_ID
rows: 1
Extra:
*************************** 14. row **********************
id: 1
select_type: PRIMARY
table: bn
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: securitymaster.bc.ASSET_ID
rows: 1
Extra:
*************************** 15. row **********************
id: 1
select_type: PRIMARY
table: de
type: ref
possible_keys: exch_cd_idx
key: exch_cd_idx
key_len: 4
ref: securitymaster.ex.EXCH_CD
rows: 1
Extra: Using index
*************************** 16. row **********************
id: 1
select_type: PRIMARY
table: ao
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: securitymaster.bc.ASSET_ID
rows: 1
Extra:
*************************** 17. row **********************
id: 1
select_type: PRIMARY
table: ev2
type: ref
possible_keys: PRIMARY,Asset_Id_idx,Event_Type_idx
key: Asset_Id_idx
key_len: 9
ref: securitymaster.ev.ASSET_ID
rows: 2
Extra:
*************************** 18. row **********************
id: 2
select_type: DEPENDENT SUBQUERY
table: ev3
type: ref
possible_keys: Asset_Id_idx,Event_Type_idx
key: Asset_Id_idx
key_len: 9
ref: securitymaster.ev2.ASSET_ID
rows: 2
Extra: Using where

Thx.

David