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

Missing index for MySQL 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-alpha5
    • Component/s: engine
    • Labels:
      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

        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