ACT_RU_VARIABLE indexing


#1

We are seeing performance degradation on this query. Does it make sense to add an index on

ACT_RU_VARIABLE TEXT, NAME? We use this query extensively to find the correct execution id.

select DISTINCT RES.* ,
P.KEY_ AS ProcessDefinitionKey,
P.ID_ AS ProcessDefinitionId,
P.NAME_ AS ProcessDefinitionName,
P.VERSION_ AS ProcessDefinitionVersion,
P.DEPLOYMENT_ID_ AS DeploymentId
FROM ACT_RU_EXECUTION RES
INNER join ACT_RE_PROCDEF P ON RES.PROC_DEF_ID_ = P.ID_
INNER join ACT_RU_VARIABLE A0 ON RES.PROC_INST_ID_ = A0.PROC_INST_ID_
WHERE RES.PARENT_ID_ IS ?
AND P.KEY_ = ?
AND A0.EXECUTION_ID_ = A0.PROC_INST_ID_
AND A0.NAME_= ?
AND A0.TYPE_ = ?
AND A0.TEXT_ = ?
ORDER by RES.ID_ asc