Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-9436

Missing not null filter for MySQL DELETE statement slows down History Cleanup

    Details

    • Type: Bug Report
    • Status: Closed
    • Priority: L3 - Default
    • Resolution: Fixed
    • Affects Version/s: 7.10.0, 7.9.5, 7.8.11
    • Fix Version/s: 7.10.0, 7.9.6, 7.8.12, 7.10.0-alpha6
    • Component/s: engine
    • Labels:
      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

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            nikola.koevski Nikola Koevski
            Reporter:
            tassilo.weidner Tassilo Weidner
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development