Details
-
Bug Report
-
Resolution: Fixed
-
L3 - Default
-
None
-
None
-
None
Description
Problem
The following update query performs poorly on MySQL:
<update id="updateByteArraysByBatchId" parameterType="java.util.Map"> update ${prefix}ACT_GE_BYTEARRAY set REMOVAL_TIME_ = #{removalTime, jdbcType=TIMESTAMP} where ID_ IN ( SELECT JOB_EXCEPTION_STACK_ID_ FROM ${prefix}ACT_HI_JOB_LOG WHERE JOB_DEF_CONFIGURATION_ = #{batchId, jdbcType=VARCHAR} ) </update>
Solution
Extend the query with the following WHERE condition:
AND JOB_EXCEPTION_STACK_ID_ IS NOT NULL
Reasoning
Original Query Plan: original-query-plan.json
Query Plan With NOT NULL condition: query-plan-with-not-null-condition.json
- The performance of the query with the NOT NULL check has increased by ~ 99 % compared to the original query
- The NOT NULL condition has the effect that the index ACT_IDX_HI_JOB_LOG_EX_STACK is used which only contains the IDs of Exception Stacktrace Byte Arrays (which is usually null for most of the historic job logs)
- The original query plan shows that the ACT_IDX_HI_JOB_LOG_JOB_CONF (corresponds idx_ACT_HI_JOB_LOG_JOB_DEF_CONFIGURATION_ in the query plan) is used which only contains JOB_DEF_CONFIGURATION_
- The property index_condition in the query plan with NOT NULL condition indicates that Index Condition Pushdown Optimization [1] was used which means that the necessary information could be retrieved from the index whereas in the original query plan the whole table needed to be scanned
Hint
It could make sense to adjust the query for MySQL only to avoid negative side-effects for other database systems
[1] https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html