I am using liferay 6.0.6 running on top of mysql and have a forum category which now holds slightly less than 2000 posts.
And performance is degrading in a bad way. I did some debugging and it seems that the Inline permission checks are to blame for this.
The query that is used to retrieve the message count looks like this
SELECT COUNT(DISTINCT MBThread.threadId) AS COUNT_VALUE
FROM MBThread
INNER JOIN MBMessage ON (MBMessage.messageId = MBThread.rootMessageId)
INNER JOIN ResourcePermission ON (
(MBMessage.userId = 10133) OR
(ResourcePermission.companyId = 10130) AND
(ResourcePermission.name = 'com.liferay.portlet.messageboards.model.MBMessage') AND
(ResourcePermission.roleId IN (0,10137)) AND
(MOD(ResourcePermission.actionIds, 2) = 1) AND
(
(
(ResourcePermission.scope = 1) AND (ResourcePermission.primKey = '10130')
) OR (
(ResourcePermission.scope = 2) AND (ResourcePermission.primKey IN ('10154'))
) OR (
(ResourcePermission.scope = 4) AND (ResourcePermission.primKey = MBMessage.messageId)
)
)
)
WHERE (MBThread.groupId = 10154) AND (MBThread.categoryId = 24105) AND (MBThread.status = 0)
When i remove the ResourcePermissions join all is well, but when i leave it in this query will take 23 seconds
I ran explain on the query and it does a full table scan on the ResourcePermission, and adding an index won't really fix much because of the way the query is written, it will still evaluate all rows
Is any of this better on the trunk ? or has anyone successfully reimplemented InlineSQLHelperImpl
