Idea: Smart groups without the cache
Just throwing this out there as something we could maybe fund:
Current behaviour
Smart groups always write to the civicrm_group_contact_cache table
Proposed behaviour
Smart groups optionally write to the civicrm_group_contact_cache table (default: yes)
Overview
As I understand it, whenever you request a smart group it stores its contacts in civicrm_group_contact_cache. This is for good reason: it's a cache that means the smart groups aren't regenerated every time they're requested, which can be slow.
It also simplifies the code: you can effectively treat smart groups the same as non-smart groups - as it's just grabbing a list of contacts from a table.
But it's possible to set the cache to expire after 0 seconds, if you need your smart groups to be up to date: ie. they are regenerated each time they're requested. I think a fair few orgs do this.
If you have a 0s cache, you sometimes don't need the data written to a table at all. You just need a transient list of contacts that doesn't need to persist past the end of the request. And it's pretty slow to use the cache table: the 'INSERT INTO' command is slow relative to the original SELECT query. This time can add up if you're querying a bunch of groups at once, or have nested smart groups, or have a lot of contacts in the groups.
So I was tentatively wondering if there could be an API4 'no-smart-group-cache' parameter, that just runs the underlying query and stores it all in memory. I imagine this would only work for API4 smart groups, as it's super-easy to get their underlying search criteria.
The default would remain to use the cache, for full backwards-compatibility. But the parameter could be used in custom code.
Two questions:
- Anyone else interested in this?
- How hard would it be / how much of a can of worms is it?