Details
-
Bug Report
-
Resolution: Fixed
-
L3 - Default
-
7.10.0, 7.9.5, 7.8.11
-
None
Description
The following DELETE statement for MySQL leads to performance issues:
-- deleteHistoricDetailsByIds_mysql delete D from ACT_HI_DETAIL D inner join ACT_HI_TASKINST T on TASK_ID_ = T.ID_ and ( T.PROC_INST_ID_ IN ( ? ) );
The DELETE statement can be improved by adding a not null filter as follows:
delete D from ACT_HI_DETAIL D inner join ACT_HI_TASKINST T on TASK_ID_ = T.ID_ and ( T.PROC_INST_ID_ IN ( ? ) ) and D.TASK_ID_ is not null;
Query plan before the DELETE statement has been improved
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 DELETE D (null) ALL ACT_IDX_HI_DETAIL_TASK_ID (null) (null) (null) 437952 100.0 Using where 1 SIMPLE T (null) eq_ref PRIMARY,ACT_IDX_HI_TASKINST_PROCINST,ACT_IDX_HI_TASKINSTID_PROCINST PRIMARY 194 magma.D.TASK_ID_ 1 5.0 Using where
Query plan after the DELETE statement has been improved
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 DELETE D (null) range ACT_IDX_HI_DETAIL_TASK_ID ACT_IDX_HI_DETAIL_TASK_ID 195 (null) 1 100.0 Using where 1 SIMPLE T (null) eq_ref PRIMARY,ACT_IDX_HI_TASKINST_PROCINST,ACT_IDX_HI_TASKINSTID_PROCINST PRIMARY 194 magma.D.TASK_ID_ 1 5.0 Using where
Setting the index results in:
- no full table scan is performed as the column type of the first row shows range instead of ALL
- the index is used as the column key of the first row is not (null) anymore but shows the used index