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

Missing index for MySQL slows down History Cleanup

XMLWordPrintable

    • Icon: Bug Report Bug Report
    • Resolution: Fixed
    • Icon: L3 - Default L3 - Default
    • 7.10.0, 7.9.6, 7.8.12, 7.10.0-alpha5
    • 7.10.0, 7.9.5, 7.8.11
    • engine
    • None

      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

        This is the controller panel for Smart Panels app

            [CAM-9435] Missing index for MySQL slows down History Cleanup

            Tassilo Weidner created issue -
            Tassilo Weidner made changes -
            Component/s New: engine [ 11656 ]
            Tassilo Weidner made changes -
            Affects Version/s New: 7.8.11 [ 15336 ]
            Affects Version/s New: 7.9.5 [ 15335 ]
            Affects Version/s New: 7.10.0 [ 15290 ]
            Tassilo Weidner made changes -
            Description Original: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}

            {code}

            The problem can be solved by adding the following index:
            {code:sql}

            {code}

            Comparing the query plans before and after the index has been set:
            Before
            After

            Setting the index results in:
            * no full table scan is performed as the column *type* for row ** shows *range* instead of *all*
            * the created index is used as the column *key* for row ** is not (null) anymore             		New: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}
            -- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Comparing the query plans before and after the index has been set:
            Before
            {code:sql}
            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)
            {code}

            After
            {code:sql}
            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)
            {code}

            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
            Tassilo Weidner made changes -
            Description Original: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}
            -- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Comparing the query plans before and after the index has been set:
            Before
            {code:sql}
            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)
            {code}

            After
            {code:sql}
            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)
            {code}

            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             		New: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}
            -- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Comparing the query plans before and after the index has been set:
            Before
            {code:none}
            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)
            {code}

            After
            {code:none}
            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)
            {code}

            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
            Tassilo Weidner made changes -
            Description Original: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}
            -- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Comparing the query plans before and after the index has been set:
            Before
            {code:none}
            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)
            {code}

            After
            {code:none}
            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)
            {code}

            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             		New: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}
            -- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Comparing the query plans before and after the index has been set:
            Before
            {noformat}
            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)
            {noformat}

            After
            {noformat}
            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)
            {noformat}

            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
            Tassilo Weidner made changes -
            Description Original: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}
            -- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Comparing the query plans before and after the index has been set:
            Before
            {noformat}
            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)
            {noformat}

            After
            {noformat}
            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)
            {noformat}

            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             		New: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}-- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Comparing the query plans before and after the index has been set:
            Before
            {noformat}
            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)
            {noformat}

            After
            {noformat}
            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)
            {noformat}

            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
            Tassilo Weidner made changes -
            Description Original: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}-- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Comparing the query plans before and after the index has been set:
            Before
            {noformat}
            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)
            {noformat}

            After
            {noformat}
            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)
            {noformat}

            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             		New: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}-- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Query plan before the index has been set
            {noformat}
            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)
            {noformat}

            Query plan before the index has been set
            {noformat}
            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)
            {noformat}

            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
            Tassilo Weidner made changes -
            Description Original: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}-- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Query plan before the index has been set
            {noformat}
            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)
            {noformat}

            Query plan before the index has been set
            {noformat}
            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)
            {noformat}

            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             		New: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}-- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Query plan before the index has been set
            {noformat}
            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)
            {noformat}

            Query plan before the index has been set
            {noformat}
            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)
            {noformat}

            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
            Tassilo Weidner made changes -
            Description Original: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}-- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Query plan before the index has been set
            {noformat}
            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)
            {noformat}

            Query plan before the index has been set
            {noformat}
            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)
            {noformat}

            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             		New: The following DELETE statement uses no index on MySQL which leads to performance issues:
            {code:sql}-- deleteHistoricDetailByteArraysByIds_mysql
            explain 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;
            {code}

            The problem can be solved by adding the following index:
            {code:sql}
            CREATE INDEX ACT_IDX_HI_DETAIL_TASK_AND_BYAR ON ACT_HI_DETAIL(BYTEARRAY_ID_, TASK_ID_);
            {code}

            Query plan before the index has been set
            {noformat}
            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)
            {noformat}

            Query plan after the index has been set
            {noformat}
            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)
            {noformat}

            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

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

                Created:
                Updated:
                Resolved: