[CAM-7441] Task Query with Authorization and Identity link are slow Created: 06/Feb/17  Updated: 15/Aug/19

Status: Open
Project: camunda BPM
Component/s: engine
Affects Version/s: 7.5.0, 7.6.0
Fix Version/s: 7.5.x, 7.6.x, 7.7.x

Type: Bug Report Priority: L3 - Default
Reporter: Christopher Zell Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related

 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.


Generated at Wed Nov 13 16:11:43 CET 2019 using JIRA 6.4.6#64021-sha1:33e5b454af4594f54560ac233c30a6e00459507e.