Details
-
Bug Report
-
Resolution: Fixed
-
L3 - Default
-
7.10.0, 7.9.5, 7.8.11
-
None
Description
The following DELETE statement uses no index on MySQL which leads to performance issues:
-- deleteHistoricDetailByteArraysByIds_mysql delete B from ACT_GE_BYTEARRAY B inner join ACT_HI_DETAIL D on B.ID_ = D.BYTEARRAY_ID_ inner join ACT_HI_TASKINST T on TASK_ID_ = T.ID_ and ( T.PROC_INST_ID_ IN ( ? ) ) WHERE D.BYTEARRAY_ID_ is not NULL;
The problem can be solved by adding the following index:
CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
Query plan before the index has been set
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE D (null) ALL ACT_IDX_HI_DETAIL_TASK_ID,ACT_IDX_HI_DETAIL_BYTEAR (null) (null) (null) 437950 50.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 1 DELETE B (null) eq_ref PRIMARY PRIMARY 194 magma.D.BYTEARRAY_ID_ 1 100.0 (null)
Query plan after the index has been set
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE D (null) range ACT_IDX_HI_DETAIL_TASK_ID,ACT_IDX_HI_DETAIL_BYTEAR,ACT_IDX_HI_DETAIL_TASK_AND_BYAR ACT_IDX_HI_DETAIL_TASK_AND_BYAR 195 (null) 218975 100.0 Using where; Using index 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 1 DELETE B (null) eq_ref PRIMARY PRIMARY 194 magma.D.BYTEARRAY_ID_ 1 100.0 (null)
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 created index is used as the column key of the first row is not (null) anymore but shows the used index