Details
-
Bug Report
-
Resolution: Unresolved
-
L3 - Default
-
None
-
7.5.0, 7.6.0
-
None
Description
The task query in combination with authorization and identity link are slow on MySQL.
To reproduce start 5000 process instances of the invoice process.
EXPLAIN SELECT Count( DISTINCT res.id_ ) FROM ACT_RU_TASK res inner join ACT_RU_IDENTITYLINK i ON i.task_id_ = res.id_ left join ACT_RE_PROCDEF procdef ON res.proc_def_id_ = procdef.id_ left join ( SELECT a.* FROM ACT_RU_AUTHORIZATION a WHERE a.type_ < 2 AND ( a.user_id_ IN ( 'demo', '*' ) OR a.group_id_ IN ( 'camunda-admin', 'management', 'accounting', 'sales' ) ) AND (( a.resource_type_ = 7 AND a.perms_ & 2 = 2 OR a.resource_type_ = 6 AND a.perms_ & 64 = 64 )) ) auth ON ( auth.resource_id_ IN ( res.id_, procdef.key_, '*' ) ) #<-- this is the problematic part WHERE res.assignee_ IS NULL AND res.assignee_ IS NULL AND i.type_ = 'candidate' AND ( i.group_id_ IN ( 'accounting', 'camunda-admin', 'management', 'sales' ) ) AND res.suspension_state_ = 1 AND ( ( res.case_execution_id_ IS NOT NULL ) OR ( auth.resource_id_ IS NOT NULL ) );
With authorization the query returns a result after 30s, without authorization after 1 s.
On 7.4 with the old authorization query it runs as well much faster.
Problem is that on this combination the existing indices are not used by MySQL.
Hint:
Since we test in the performance test suite the task query with authorization and 50k task.
I assume that the combination with the identity link table is the problem.
