Add index on task.executed?

[Guido Wimmel <gu...@gmx.net>]

Hi,

in our Syncope installation (1.1.3 on Tomcat/MySQL) we observed long execution times (>1s) for TaskDAO.findToExec(),
which is called periodically from NotificationJob to determine which notifications should be sent.

According to our analysis, this is due to the fact that TaskDAO.findToExec() performs a full scan
on all entries of the task table with DTYPE="NotificationTask" (which could be a large number as
all executed tasks are kept), although only very few tasks are selected (those with executed=0).

Creating an index on task.executed solved this problem.
Would it be a good idea to add such an index to the indexes added by default
to the database (via indexes.xml)?

Cheers,
   Guido

Re: Add index on task.executed?

[Francesco Chicchiriccò <il...@apache.org>]

> Hi,
> 
> in our Syncope installation (1.1.3 on Tomcat/MySQL) we observed long
> execution times (>1s) for TaskDAO.findToExec(),
> which is called periodically from NotificationJob to determine which
> notifications should be sent.
> 
> According to our analysis, this is due to the fact that
> TaskDAO.findToExec() performs a full scan
> on all entries of the task table with DTYPE="NotificationTask" (which
> could be a large number as
> all executed tasks are kept), although only very few tasks are
> selected (those with executed=0).
> 
> Creating an index on task.executed solved this problem.
> Would it be a good idea to add such an index to the indexes added by
> default
> to the database (via indexes.xml)?

+1

Would you mind to file an issue on JIRA and provide a patch against the branch 1_1_X?

Regards.
-- 
Francesco Chicchiriccò

ASF Member, Apache Syncope PMC chair, Apache Cocoon PMC Member
http://people.apache.org/~ilgrosso/