Re: FORCE INDEX use?
I changed the query to this
SELECT DISTINCT ':16R:GENL', CONCAT(':20C::CORP//', 'FI', ev.Event_Id), CONCAT(':20C::SEME//', ev.Event_Id),
CONCAT( ':23G:', CASE WHEN bc.ASSET_STATUS_CD='CAN' THEN 'WITH' WHEN ev.Cancel_Msg=1 THEN 'CANC'
WHEN (ev2.Event_Type IS NULL OR ADDDATE(ev2.Event_Time, 14) < ev.Event_Time) THEN 'NEWM' ELSE 'REPL' END),
CONCAT(':22F::CAEV//', ev.EVENT_CD), CONCAT(':22F::CAMV//', 'MAND'), CONCAT(':25D::PROC//', 'COMP'), ':16S:GENL', ':16R:USECU',
COALESCE(CONCAT(':35B:ISIN ', x1.id_number), CONCAT('/CSP/ ', x2.id_number), CONCAT('/SED/ ', x3.id_number), CONCAT('/COM/ ', x4.id_number), ':35B:ISIN UKWN'),
bc.security_name,
':16R:FIA', CONCAT(':94B::PLIS//EXCH/', IF(de.MIC_CD IS NULL, 'UKWN', de.MIC_CD)),
IF(ev.EVENT_CD = 'INTR', CONCAT(':22F:MICO/', mm.ISO_cd), NULL), CONCAT(':11A::DENO//', bc.CURRENCY_CD), ':16S:FIA',
':16R:ACCTINFO', IF(ev.Event_Cd IN ('LIQU', 'DFLT'),':97A::SAFE//GENR',':97A::SAFE//NONREF'), ':16S:ACCTINFO', ':16S:USECU',
':16R:CADETL',
IF(ev.EVENT_CD IN ('INTR', 'LIQU') AND ci.int_eff_dt <= NOW() AND (rate_reset_freq_cd IS NULL OR pay_freq_cd=rate_reset_freq_cd),
CONCAT(':92F::GRSS//', ci.pay_int_curr_cd, bc.current_cpn), NULL),
IF(ev.EVENT_CD IN ('INTR', 'LIQU') AND (pay_freq_cd!=rate_reset_freq_cd), ':92F::GRSS//UKWN', NULL),
IF(ev.EVENT_CD IN ('INTR', 'LIQU') AND ci.int_eff_dt = NOW() AND (ci.rate_reset_freq_cd IS NULL OR ci.pay_freq_cd=ci.rate_reset_freq_cd),
CONCAT(':92F::GRSS//', ci.pay_int_curr_cd, bc.current_cpn), NULL),
IF(ev.EVENT_CD = 'REDM', CONCAT(':98A::REDM//', DATE_FORMAT(ao.amt_out_dt, '%Y%m%d')), NULL),
IF(ev.EVENT_CD = 'CPST', CONCAT(':98A::COUC//', DATE_FORMAT(bi.orig_iss_dt, '%Y%m%d')), NULL),
IF(ev.EVENT_CD IN ('DFLT', 'BRUP'), CONCAT(':98A::EFFD//', DATE_FORMAT(NOW(), '%Y%m%d')), NULL),
IF(ev.EVENT_CD = 'CPNR', CONCAT(':98A::EFFD//', DATE_FORMAT(NOW(), '%Y%m%d')), NULL),
IF(ev.EVENT_CD = 'NAME', CONCAT(':98A::EFFD//', DATE_FORMAT(NOW(), '%Y%m%d')), NULL),
IF(ev.EVENT_CD = 'REDM', CONCAT(':98A::REDM//', COALESCE(DATE_FORMAT(ao.amt_out_dt, '%Y%m%d'), DATE_FORMAT(bc.maturity_date, '%Y%m%d'), 'UKWN')), NULL),
IF(ev.EVENT_CD = 'INTR',':69A::INPE//UKWN', NULL),
IF(ev.EVENT_CD IN ('INTR', 'CNPR'), CONCAT(':92A::INTR//PRCT', TRIM(TRAILING '0' FROM REPLACE(bc.current_cpn, '.', ','))), NULL),
IF(ev.EVENT_CD = 'NAME', CONCAT(':70E::NAME//', COALESCE(ii.issuer_short_name, ii.issuer_long_name)), NULL),
':16S:CADETL',
IF(ev.EVENT_CD IN ('INTR', 'REDM', 'CPST', 'MCAL'), ':16R:CAOPTN', NULL),
IF(ev.EVENT_CD IN ('INTR', 'REDM', 'CPST', 'MCAL'),':13A::CAON//001', NULL),
IF(ev.EVENT_CD IN ('INTR', 'REDM', 'MCAL'), ':22F::CAOP//CASH', NULL),
IF(ev.EVENT_CD = 'CPST', ':22F::CAOP//SECU', NULL),
IF(ev.EVENT_CD IN ('INTR', 'REDM'), CONCAT(':11A::OPTN//', ci.pay_int_curr_cd), NULL),
IF(ev.EVENT_CD IN ('INTR', 'REDM', 'CPST'), ':17B::DFLT//Y', NULL),
IF(ev.EVENT_CD IN ('INTR', 'LIQU'), ':98A::PAYD//UKWN', NULL),
IF(ev.EVENT_CD = 'REDM', CONCAT(':90A::REDM//PRCT/', bi.redemption_val), NULL),
IF(ev.EVENT_CD IN ('INTR', 'REDM', 'CPST', 'MCAL'), ':16S:CAOPTN', NULL),
IF(bn.NOTE IS NULL, NULL, ':16R:ADDINFO'),
IF(bn.NOTE IS NULL, NULL, CONCAT(':70E::ADTX//',SUBSTRING(bn.NOTE,1,35))),
IF(SUBSTRING(bn.NOTE,36,35)='',NULL,SUBSTRING(bn.NOTE,36,35)),
IF(SUBSTRING(bn.NOTE,71,35)='',NULL,SUBSTRING(bn.NOTE,71,35)),
IF(SUBSTRING(bn.NOTE,106,35)='',NULL,SUBSTRING(bn.NOTE,106,35)),
IF(SUBSTRING(bn.NOTE,141,35)='',NULL,SUBSTRING(bn.NOTE,141,35)),
IF(SUBSTRING(bn.NOTE,176,35)='',NULL,SUBSTRING(bn.NOTE,176,35)),
IF(SUBSTRING(bn.NOTE,211,35)='',NULL,SUBSTRING(bn.NOTE,211,35)),
IF(SUBSTRING(bn.NOTE,246,35)='',NULL,SUBSTRING(bn.NOTE,246,35)),
IF(SUBSTRING(bn.NOTE,281,35)='',NULL,SUBSTRING(bn.NOTE,281,35)),
IF(SUBSTRING(bn.NOTE,316,35)='',NULL,SUBSTRING(bn.NOTE,316,35)),
IF(bn.NOTE IS NULL, NULL, ':16S:ADDINFO')
FROM corax_event ev INNER JOIN bond_core_information bc ON ev.ASSET_ID = bc.ASSET_ID
INNER JOIN fi_issuer_information ii ON bc.issuer_id = ii.issuer_id
LEFT OUTER JOIN fi_cross_ref_data_identifiers x1 ON x1.asset_id = ev.asset_id AND x1.id_cd = 'ISN' AND x1.ACTIVE = 1
LEFT OUTER JOIN fi_cross_ref_data_identifiers x2 ON x2.asset_id = ev.asset_id AND x2.id_cd = 'CSP' AND x2.ACTIVE = 1
LEFT OUTER JOIN fi_cross_ref_data_identifiers x3 ON x3.asset_id = ev.asset_id AND x3.id_cd = 'COM' AND x3.ACTIVE = 1
LEFT OUTER JOIN fi_cross_ref_data_identifiers x4 ON x4.asset_id = ev.asset_id AND x4.id_cd = 'SED' AND x4.ACTIVE = 1
LEFT OUTER JOIN coupon_information ci ON ci.ASSET_ID = bc.ASSET_ID
LEFT OUTER JOIN coupon_reset_schedule cs ON cs.ASSET_ID = bc.ASSET_ID
LEFT OUTER JOIN MICO_map mm ON mm.day_count_cd = ci.day_count_cd
LEFT OUTER JOIN exchange_listings ex ON ex.ASSET_ID = bc.ASSET_ID
LEFT OUTER JOIN bond_issuance_summary bi ON bi.ASSET_ID = bc.ASSET_ID
LEFT OUTER JOIN bonds_note_information bn ON bn.ASSET_ID = bc.ASSET_ID AND bn.note_eff_dt >= cp.Last_Run
LEFT OUTER JOIN dsfi_exch_mic_map de ON de.exch_cd = ex.exch_cd
LEFT OUTER JOIN amt_outstanding_hist_info ao ON bc.ASSET_ID = ao.ASSET_ID AND ao.amt_outsd = 0
LEFT OUTER JOIN corax_event ev2 ON ev.ASSET_ID = ev2.ASSET_ID AND ev.Event_Type = ev2.Event_Type AND ev2.Event_Id <> ev.Event_Id AND
ev2.Event_Id = (SELECT MAX(ev3.Event_Id) FROM corax_event ev3 WHERE ev2.ASSET_ID = ev3.ASSET_ID AND ev2.Event_Type = ev3.Event_Type
AND ev.Event_Id<>ev3.Event_Id AND ev2.Event_Id < ev.Event_Id)
INNER JOIN customer_watch_list cw ON ev.SECURITY_ID = cw.SecurityId
INNER JOIN customer_portfolio cp ON cw.Portfolio_Id = cp.Portfolio_Id
WHERE ev.EVENT_TYPE IN ( 4, 5, 6) AND ev.Event_Time > cp.Last_Run
Explain plan is
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","cp","ALL","PRIMARY","",,"",1,"Using temporary"
1,"PRIMARY","cw","ref","PRIMARY,FK_customer_watch_list_2","PRIMARY",4,"securitymaster.cp.Portfolio_Id",31528,"Using index"
1,"PRIMARY","ev","ref","Asset_Id_idx,Security_Id_idx,Event_Type_idx,Event_Time_idx","Security_Id_idx",9,"securitymaster.cw.SecurityId",5,"Using where"
1,"PRIMARY","bc","eq_ref","PRIMARY,FK_BOND_CORE_INFO_FI_ISSUER_INFO","PRIMARY",8,"securitymaster.ev.ASSET_ID",1,"Using where"
1,"PRIMARY","ii","eq_ref","PRIMARY","PRIMARY",8,"securitymaster.bc.ISSUER_ID",1,""
1,"PRIMARY","x1","ref","IX_ID_EFF_DATE","IX_ID_EFF_DATE",11,"securitymaster.ev.ASSET_ID,const",1,""
1,"PRIMARY","x2","ref","IX_ID_EFF_DATE","IX_ID_EFF_DATE",11,"securitymaster.ev.ASSET_ID,const",1,""
1,"PRIMARY","x3","ref","IX_ID_EFF_DATE","IX_ID_EFF_DATE",11,"securitymaster.ev.ASSET_ID,const",1,""
1,"PRIMARY","x4","ref","IX_ID_EFF_DATE","IX_ID_EFF_DATE",11,"securitymaster.ev.ASSET_ID,const",1,""
1,"PRIMARY","ci","ref","COUPON_INFORMATION_FK_1","COUPON_INFORMATION_FK_1",8,"securitymaster.bc.ASSET_ID",1,""
1,"PRIMARY","cs","ref","PRIMARY","PRIMARY",8,"securitymaster.bc.ASSET_ID",1,"Using index"
1,"PRIMARY","mm","eq_ref","PRIMARY","PRIMARY",1,"securitymaster.ci.DAY_COUNT_CD",1,""
1,"PRIMARY","ex","ref","PRIMARY","PRIMARY",8,"securitymaster.bc.ASSET_ID",1,"Using index"
1,"PRIMARY","bi","eq_ref","PRIMARY","PRIMARY",8,"securitymaster.bc.ASSET_ID",1,""
1,"PRIMARY","bn","ref","PRIMARY","PRIMARY",8,"securitymaster.bc.ASSET_ID",1,""
1,"PRIMARY","de","ref","exch_cd_idx","exch_cd_idx",4,"securitymaster.ex.EXCH_CD",1,"Using index"
1,"PRIMARY","ao","ref","PRIMARY","PRIMARY",8,"securitymaster.bc.ASSET_ID",1,""
1,"PRIMARY","ev2","ref","PRIMARY,Asset_Id_idx,Event_Type_idx","Event_Type_idx",5,"securitymaster.ev.Event_Type",3048,""
2,"DEPENDENT SUBQUERY","ev3","ref","Asset_Id_idx,Event_Type_idx","Event_Type_idx",5,"securitymaster.ev2.Event_Type",3048,"Using where"
I note that the highest number of rows now used (31528) is the cp table and is much more reasonable. The problem remains however that it takes over 15 minutes to run ( I kill it then as it is not a usable query in my app at the point it takes longer than 1 minute to run).
Is there no way of improving the performance of MySQL for this query?
Thx.
David