# Slow performance using task search API

**URL:** <https://forum.flowable.org/t/slow-performance-using-task-search-api/2159>\
**Category:** Flowable Engine\
**Created:** [June 25, 2018, 10:52pm UTC](https://forum.flowable.org/t/slow-performance-using-task-search-api/2159 "2018-06-25T22:52:18Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![schmke](https://avatars.discourse-cdn.com/v4/letter/s/d78d45/32.png) [@schmke](https://forum.flowable.org/u/schmke)\
**Post date:** [June 25, 2018, 10:52pm UTC](https://forum.flowable.org/t/slow-performance-using-task-search-api/2159/1 "2018-06-25T22:52:19Z")

</div>

I am using Flowable 5.23 with a system with ~230K tasks resident and am perform searches using the task search API and seeing performance that is quite a bit slower than I’d expect or hope.

First, I’m doing a pretty basic search for tasks assigned to a user or candidates for them (directly or through a group) and as this is supporting a UI that is able to make paginated requests, asking for just the first 25. The generated SQL I see from turning on debug logging is:

select distinct RES.\*  
from ACT\_RU\_TASK RES  
left join ACT\_RU\_IDENTITYLINK I on I.TASK\_ID\_ = RES.ID\_  
WHERE (RES.ASSIGNEE\_ = ‘user1’  
or (RES.ASSIGNEE\_ is null  
and I.TYPE\_ = ‘candidate’  
and (I.USER\_ID\_ = ‘user1’  
or I.GROUP\_ID\_ IN (‘group1’) )))  
order by RES.ID\_ asc  
LIMIT 25 OFFSET 0  
;

This looks perfectly benign, but takes 4-5 seconds to execute.

The issue seems to be the order by and MySQL having to create a temporary table to sort it as removing the order by results in a response in 20-30 milliseconds.

The problem is, I’m not setting an order by, this seems to be the default.

So, is there a way to get the order by to not be included?

Now, in reality I probably do want it ordered, and perhaps on other columns, but those queries are just as slow or slower, for example ordering on CREATE\_TIME\_ results in the query time going just over 5 seconds.

Are others not using MySQL and/or not using it with this many tasks or with the task search API like this? Or is this normal?

Now worse, at times I need to do searches on some process variables. For instance, I’d like to get tasks that meet the above criteria, but also have a score variable with a value of at least 45. This is easy enough to do with the task search API, I can just use the processVariableValueGreaterThan method. The resulting SQL is:

select distinct RES.\*  
from ACT\_RU\_TASK RES  
left join ACT\_RU\_IDENTITYLINK I on I.TASK\_ID\_ = RES.ID\_  
inner join ACT\_RU\_VARIABLE A0 on RES.PROC\_INST\_ID\_ = A0.PROC\_INST\_ID\_  
WHERE A0.TASK\_ID\_ is null  
and A0.NAME\_= ‘score’  
and A0.TYPE\_ = ‘double’  
and A0.DOUBLE\_ \> 45.0  
and (RES.ASSIGNEE\_ = ‘user1’  
or (RES.ASSIGNEE\_ is null  
and I.TYPE\_ = ‘candidate’  
and (I.USER\_ID\_ = ‘user1’  
or I.GROUP\_ID\_ IN ('group1) )))  
order by RES.ID\_ asc  
LIMIT 25 OFFSET 0  
;

This looks like you’d expect, but takes a whopping 52 seconds.

This again is caused by the order by, removing it results in a response is about 50 milliseconds.

So it seems MySQL just does really poorly with queries that need to be ordered when the query is a little complex and it decides it can’t use an index to do the ordering.

Has anyone else seen this behavior? If so, how have you addressed it?

Or are there any suggestions on how to use the task search API and get good performance in this scenario? Or am I stuck coming up with my own SQL generation/queries that craft SQL that performs better, perhaps by avoiding order by?