Data

SQL Query:

    
/* 'IN' Strategy
   SELECT ...
   left join (...)
   AUTH ON (AUTH.RESOURCE_ID_ in (RES.ID_, PROCDEF.KEY_, '*'))   
*/
EXPLAIN select count(distinct RES.ID_ )
    from ACT_RU_TASK RES
      inner join ACT_RE_PROCDEF D on RES.PROC_DEF_ID_ = D.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 ( 'kermit', '*')           
      OR A.GROUP_ID_ IN  (  'Gerentes' , 'Atendentes' , 'AtendentesSAC' , 'Bko-formalizacao' , 'Backoffices' ) 
    )
      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_, '*'))   
 -- AUTH ON (AUTH.RESOURCE_ID_ = RES.ID_ OR AUTH.RESOURCE_ID_ = D.KEY_ OR AUTH.RESOURCE_ID_ = '*' )

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	RES	NULL	index	ACT_FK_TASK_PROCDEF	ACT_FK_TASK_PROCDEF	195	NULL	10981	100.00	Using where; Using index
1	SIMPLE	D	NULL	eq_ref	PRIMARY	PRIMARY	194	camunda.RES.PROC_DEF_ID_	1	100.00	Using index
1	SIMPLE	PROCDEF	NULL	ALL	PRIMARY	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	A	NULL	ALL	ACT_UNIQ_AUTH_USER,ACT_UNIQ_AUTH_GROUP,ACT_IDX_AUTH_GROUP_ID,ACT_IDX_AUTH_RESOURCE_ID	NULL	NULL	NULL	9800	100.00	Range checked for each record (index map: 0x1E)