# ACT\_RU\_VARIABLE indexing

**URL:** <https://forum.flowable.org/t/act-ru-variable-indexing/3178>\
**Category:** Flowable Engine\
**Created:** [January 4, 2019, 2:02pm UTC](https://forum.flowable.org/t/act-ru-variable-indexing/3178 "2019-01-04T14:02:59Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![matthetterich](https://avatars.discourse-cdn.com/v4/letter/m/53a042/32.png) [@matthetterich](https://forum.flowable.org/u/matthetterich)\
**Post date:** [January 4, 2019, 2:03pm UTC](https://forum.flowable.org/t/act-ru-variable-indexing/3178/1 "2019-01-04T14:03:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![stu](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@stu](https://forum.flowable.org/u/stu)\
**Post date:** [January 12, 2021, 8:19am UTC](https://forum.flowable.org/t/act-ru-variable-indexing/3178/2 "2021-01-12T08:19:35Z")

</div>

Same question here. Can anyone offer any advice?

---

<div class="post-metadata">

**Author:** ![joram](https://yyz1.discourse-cdn.com/flex035/user_avatar/forum.flowable.org/joram/32/26_2.png) [@joram](https://forum.flowable.org/u/joram)\
**Post date:** [January 19, 2021, 9:20am UTC](https://forum.flowable.org/t/act-ru-variable-indexing/3178/3 "2021-01-19T09:20:29Z")

</div>

> [@matthetterich](#):
>
> Does it make sense to add an index on
> 
> ACT\_RU\_VARIABLE TEXT, NAME?

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?

---

<div class="post-metadata">

**Author:** ![matthetterich](https://avatars.discourse-cdn.com/v4/letter/m/53a042/32.png) [@matthetterich](https://forum.flowable.org/u/matthetterich)\
**Post date:** [January 19, 2021, 1:58pm UTC](https://forum.flowable.org/t/act-ru-variable-indexing/3178/4 "2021-01-19T13:58:07Z")

</div>

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.