We are using the community version of Camunda (v7.11) and the below query gets triggered from Camunda when we call the GET /task endpoint without passing any query parameters. This comes handy when we try to get the complete list without pagination but when we have data size of over 100K, the query gets stuck and goes into the sending data state for a long period of time when we checked the process list from the MySQL CLI. Is there a way to further optimise this query by creating an index in the MySQL for getting better performance with increasing data size and to avoid the query getting stuck in the sending data state for a long period of time.
select distinct
RES.REV_, RES.ID_, RES.NAME_, RES.PARENT_TASK_ID_, RES.DESCRIPTION_, RES.PRIORITY_,
RES.CREATE_TIME_, RES.OWNER_, RES.ASSIGNEE_, RES.DELEGATION_, RES.EXECUTION_ID_,
RES.PROC_INST_ID_, RES.PROC_DEF_ID_, RES.CASE_EXECUTION_ID_, RES.CASE_INST_ID_,
RES.CASE_DEF_ID_, RES.TASK_DEF_KEY_, RES.DUE_DATE_, RES.FOLLOW_UP_DATE_,
RES.SUSPENSION_STATE_, RES.TENANT_ID_
from ACT_RU_TASK RES
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 ( 'accounting' , 'sales' , 'camunda-admin' , 'management' )
)
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_, '*'))
WHERE (
1 = 1
)
and (
(RES.CASE_EXECUTION_ID_ IS NOT NULL)
OR
(AUTH.RESOURCE_ID_ IS NOT NULL)
)
order by RES.ID_ asc
LIMIT 2147483647 OFFSET 0