FORCE INDEX use?
This was posted in Newbie but it was suggested I post here for more 'expert' eyes.
I have complex query causing me all sorts of grief. Here it is
SELECT DISTINCT *
FROM corax_event ev FORCE INDEX (Event_Time_idx) 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 AND ev.Event_Time > cp.Last_Run
WHERE ev.EVENT_TYPE IN ( 4, 5, 6)
The reason I tried the FORCE INDEX on the ev table is that it has 1.5M rows in SELECT COUNT(*) FROM CORAX_EVENT ev WHERE ev.Event_Type IN (4, 5, 6). It scans the table anyway, taking days to run. Here is the EXPLAIN output.
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","ev","ALL","Event_Time_idx","",,"",1561043,"Using where; Using temporary"
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","cw","ref","PRIMARY,FK_customer_watch_list_2","FK_customer_watch_list_2",8,"securitymaster.ev.SECURITY_ID",1,"Using index"
1,"PRIMARY","cp","eq_ref","PRIMARY","PRIMARY",4,"securitymaster.cw.Portfolio_Id",1,"Using where"
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","Asset_Id_idx",9,"securitymaster.ev.ASSET_ID",3,""
2,"DEPENDENT SUBQUERY","ev3","ref","Asset_Id_idx,Event_Type_idx","Asset_Id_idx",9,"securitymaster.ev2.ASSET_ID",3,"Using where"
Here is the table DDL
DROP TABLE IF EXISTS `securitymaster`.`corax_event`;
CREATE TABLE `corax_event` (
`Event_Id` bigint(20) unsigned NOT NULL auto_increment,
`ASSET_ID` bigint(20) unsigned default NULL,
`RIC` varchar(20) default NULL,
`SECURITY_ID` bigint(20) unsigned default NULL,
`RIC_Source` int(10) unsigned default NULL,
`Cap_Chng_Market_Lvl_Id` bigint(20) unsigned default NULL,
`Previous_Event_Id` bigint(20) unsigned default NULL,
`Event_Type` int(10) unsigned default NULL,
`FI_ISSUER_ID` bigint(20) unsigned default NULL,
`EQ_ISSUER_ID` bigint(20) unsigned default NULL,
`EPS_ID` bigint(20) unsigned default NULL,
`PILC` bigint(20) unsigned default NULL,
`TSO_Event_Id` bigint(20) unsigned default NULL,
`Event_Time` datetime NOT NULL default '0000-00-00 00:00:00',
`Cancel_Msg` tinyint(3) unsigned default '0',
`Event_CD` varchar(4) default '',
`Cap_Event_Type` tinyint(3) unsigned default NULL,
`Div_Feature` tinyint(3) unsigned default NULL,
`Div_Type_Marker` tinyint(3) unsigned default NULL,
`Record_Date` date default NULL,
`Message` varchar(255) default NULL,
`Div_Market_Lvl_Id` bigint(20) unsigned default NULL,
`ISIN` varchar(12) default NULL,
PRIMARY KEY (`Event_Id`),
KEY `Asset_Id_idx` (`ASSET_ID`),
KEY `Security_Id_idx` (`SECURITY_ID`),
KEY `FI_Issuer_idx` (`FI_ISSUER_ID`),
KEY `EQ_Issuer_Idx` (`EQ_ISSUER_ID`),
KEY `Cap_Chng_Market_idx` (`Cap_Chng_Market_Lvl_Id`),
KEY `RIC_idx` (`RIC`),
KEY `Event_Type_idx` (`Event_Type`),
KEY `Event_Time_idx` (`Event_Time`),
KEY `Div_Market_Lvl_idx` (`Div_Market_Lvl_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 1634304 kB; InnoDB free: 623616 kB; InnoDB free';
Better ideas on approach for this? Explanation as to why it does not use the Event_Time_idx?
Thx.
David