Need database Indices to Optimize proc_inst_changed trigger

[galenatjpl]

The proc_inst_changed trigger can be invoked hundreds of thousands of times in a heavy load situation, and this can lead to lock wait timeouts on the database.

Analysis on the inner query revealed that adding two new indices:

create index PARENT_ACT_INST_ID_ on ACT_HI_ACTINST (`PARENT_ACT_INST_ID_`);
create index ACT_TYPE_ on ACT_HI_ACTINST (`ACT_TYPE_`);

would greatly improve the situation, by getting rid of full table scans on the internal portion of the query.

So adding these indices, and testing is the thing to do here.
The question was asked to the Camunda Team, as to whether adding these indices would pose any potential issues, and the response was : "This should not cause any issues, however if your history tables are very large, you may want to also look into scheduling a history clean up.". We will be fixing the history cleanup issue (#18), as well, so this will be fine.

ANALYSIS:
Then re-ran the EXPLAIN SELECT that was showing the full table scan above.
Here’s the before, and after:

BEFORE:

MariaDB [cws]> EXPLAIN SELECT * FROM ACT_HI_PROCINST PI LEFT JOIN ACT_HI_ACTINST AI ON PI.PROC_INST_ID_ = AI.PARENT_ACT_INST_ID_ WHERE (PI.END_TIME_ IS NOT NULL AND AI.ACT_TYPE_ IN ('noneEndEvent' , 'endEvent', 'escalationEndEvent', 'compensationEndEvent', 'signalEndEvent', 'terminateEndEvent')) AND PI.PROC_INST_ID_ = '00003323-784d-11eb-894c-06dbfd520c8a';
+------+-------------+-------+-------+---------------------------------------+---------------+---------+-------+---------+-------------+
| id   | select_type | table | type  | possible_keys                         | key           | key_len | ref   | rows    | Extra       |
+------+-------------+-------+-------+---------------------------------------+---------------+---------+-------+---------+-------------+
|    1 | SIMPLE      | PI    | const | PROC_INST_ID_,ACT_IDX_HI_PRO_INST_END | PROC_INST_ID_ | 194     | const |       1 |             |
|    1 | SIMPLE      | AI    | ALL   | NULL                                  | NULL          | NULL    | NULL  | 6616521 | Using where |
+------+-------------+-------+-------+---------------------------------------+---------------+---------+-------+---------+-------------+
2 rows in set (0.05 sec)

AFTER:

MariaDB [cws]> EXPLAIN SELECT * FROM ACT_HI_PROCINST PI LEFT JOIN ACT_HI_ACTINST AI ON PI.PROC_INST_ID_ = AI.PARENT_ACT_INST_ID_ WHERE (PI.END_TIME_ IS NOT NULL AND AI.ACT_TYPE_ IN ('noneEndEvent' , 'endEvent', 'escalationEndEvent', 'compensationEndEvent', 'signalEndEvent', 'terminateEndEvent')) AND PI.PROC_INST_ID_ = '00003323-784d-11eb-894c-06dbfd520c8a';
+------+-------------+-------+-------+---------------------------------------+---------------+---------+-------+------+------------------------------------+
| id   | select_type | table | type  | possible_keys                         | key           | key_len | ref   | rows | Extra                              |
+------+-------------+-------+-------+---------------------------------------+---------------+---------+-------+------+------------------------------------+
|    1 | SIMPLE      | PI    | const | PROC_INST_ID_,ACT_IDX_HI_PRO_INST_END | PROC_INST_ID_ | 194     | const |    1 |                                    |
|    1 | SIMPLE      | AI    | ref   | ACT_TYPE_,PARENT_ACT_INST_ID_   | PARENT_ACT_INST_ID_ | 195     | const |    5 | Using index condition; Using where |
+------+-------------+-------+-------+---------------------------------------+---------------+---------+-------+------+------------------------------------+
2 rows in set (0.05 sec)
MariaDB [cws]> SELECT * FROM ACT_HI_PROCINST PI LEFT JOIN ACT_HI_ACTINST AI ON PI.PROC_INST_ID_ = AI.PARENT_ACT_INST_ID_ WHERE (PI.END_TIME_ IS NOT NULL AND AI.ACT_TYPE_ IN ('noneEndEvent' , 'endEvent', 'escalationEndEvent', 'compensationEndEvent', 'signalEndEvent', 'terminateEndEvent')) AND PI.PROC_INST_ID_ = '00003323-784d-11eb-894c-06dbfd520c8a';
Empty set (0.06 sec)

You can see the “after” is NOT looking at the 6 million rows, and furthermore it’s returning the select in 0.06 seconds. (edited)