-
Bug Report
-
Resolution: Unresolved
-
L3 - Default
-
None
-
7.5.0, 7.6.0
-
None
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.
