Hi everyone!
I am a total begginner in SuiteCRM and we are trying to get rid of some performance issues in our production environment.
So after a little digging, we found this query that is taking over 2 secs to return:
SELECT [id] FROM [aop_case_updates] WHERE [aop_case_updates].[case_id]=@1 AND [aop_case_updates].[deleted]=@2
Since it is a simple select on a FK, our immediate thought was to create an index on it (case_id, deleted).
Should we do it directly in the database, or altering vardefs + Repair?
If vardefs is the way to go, should we merge this to github, or it is too specific to our installation?
I would do it in the vardefs. In the Quick Repair & Rebuild, don’t forget to scroll down to the bottom and execute the query it proposes to you.
If you see a vast performance improvement, I think it’s a good reason to create a PR to add this to core. I guess anybody that uses case updates should have the same kind of issues.
Don’t forget also to clean-up the database of old/unneeded stuff, or orphaned updates (from cases that were already deleted). See this blog post.
Unfortunately, we didn’t get to try this.
We bumped into some other concurrent queries, and rescheduled them. Now the delay is “acceptable” to the user.
