We are using flowable 6.5.0 for modeling apps (processes + forms).
We have around 10 flowable processes currently running in production. Each of those 10 have atleast 10 user-tasks with forms attached. With more to come in near future.
Every day around 5-10k new process instances are created. and so around ~50k new form instances are created in act_fo_form_instance every day.
Currently we are having problem with form instance select query. It is taking too much time as compared to other queries. As a comparison: act_fo_form_instance select query is taking ~600ms avg time to return result while task select takes ~50 ms avg.
Currently our act_fo_form_instance table has 1M+ rows. and so I checked and found out that it doesnt have any index on form_definition_id and task_instance_id columns. Form engine uses these two columns to find the relevant form for a task. We use taskService.getTaskFormModel(taskId) to get the relevant form for the task.
I have attached two images below. First one shows the avg time graph for form instance select. Second one shows the breakdown for our api while it does multiple operations. As you can see form-instance select takes most of the time.
Should we create a new index in act_fo_form_instances table manually?
or, should we delete excess data from act_fo_form_instance table? if so, is there any way to do it via cron jobs like history cleaning which was introduced in flowable 6.5.0