ACT_RU_VARIABLE indexing

Flowable Engine
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

2

Same question here. Can anyone offer any advice?

3

Indices on text values are typically not very useful, as the text_ column can be very large and does not contain values such as ids.

What use case are you trying to solve? Have you tried whether querying using two queries isn’t faster?

4

I can tell you that we had no choice but to add this index. We needed to send messages to our processes and we needed to look up the processes based on variables stored on the process. We did not want flowable identifiers bleeding into our platform. Additionally, we do not store large values in the TEXT_ attribute. We only store identifiers on the process and look up data when we need it. This technique has bee very successful for us.