Claremont Sakai was s also affected by this query last week and our users in the sites could not access Forums tool almost for two hours. Affected two sites are not even large sites. They have only 42 and 47 users respectively. They have a huge Forums threads, though.

We're seeing horrific performance when MySQL selects a bad query plan and are going to try SAK-29571.

This query does get cached through, so future requests are much faster, could some of the performance problems with large site be because it's effectively overflowing the mysql query cache and so it never manages to cache the data for a whole site?

As a temporary fix, a sakai.property will be introduced to disable ranks for these performance concerns in two phases:
will hide the feature and prevent the findAuthoredMessageCountForStudent query from occurring in sites that do not have ranks. This means the queries will continue in any sites that already have ranks.
will disable ranks throughout the code, blocking out the query completely

For reference: the named query is findAuthoredMessageCountForStudent in
messageforums-hbm/src/java/org/sakaiproject/component/app/messageforums/dao/hibernate/MessageImpl.hbm.xml

Yeah it does reference the exact same query, but the issue reported in that ticket is where MySQL optimizer selects a bad query plan using the MFR_MESSAGE_DELETED_I index.

If MySQL doesn't use the MFR_MESSAGE_DELETED_I index as part of the query plan then you would not be affected performance wise by this query.