5.44+ Unsubscribe from Smart Group form generates excessive process load

Overview

The recent change to email unsubscribe from Smart Group is overloading mysql. This change by @mattwire is providing a check to see which smart group an unsubscribing user is in before unsubscribing them. On a large mailing, site with a lot of smart groups, or even one complex smart this code is a time bomb. Especially as we see a rise in automated link checking bots on large email providers.

Current behavior

When a email recipient clicks unsubscribe all smart groups are regenerated. When CRM_Contact_BAO_GroupContactCache is called all smart groups are regenerated regardless of the setting of cache time. Also this is running even if the email doesn't contain any smart groups. Then the cache table is Left Joined onto the query for a search. Which also can be refreshed by another user clicking the link in the 5-10 second process time of a large site with large smart groups.

This is an incredibly high process load before any action is actually taken in order to display a group title on page that 9 times out of 10 is a bot not an end user.

Proposed behavior

Members do not need to see the Group labels from which they are unsubscribing, and if they are unsubscribing from this Email list then it is reasonable to mark them as unsubscribed from a smart group used in the mailing even if they are not in it. If they in the future become a member of the smart group, they probably don't want email about it.

It is reasonable to run this process AFTER the unsubscribe button is clicked by the user. Running it on page load is just wasting a ton resources on bot traffic.

Comments

@mattwire 's solution is reasonable for many, but it just isn't scaling well on larger civicrm installs. We have either reverted the code or switched to Opt Out of all emails for those larger clients, but those are temporary solutions.