Details
-
Type: Improvement
-
Status: Done/Fixed
-
Priority: Minor
-
Resolution: Duplicate
-
Affects Version/s: 4.6.6
-
Fix Version/s: Unscheduled
-
Component/s: Core CiviCRM
-
Labels:
-
Documentation Required?:None
-
Funding Source:Needs Funding
Description
Currently users can manually add and remove contacts from smart groups.
This is a problem for two reasons:
1. Smart groups become unmanageable because manually added/removed cause confusion - does the smart group actually follow the saved search criteria or not?
Smart groups should be strictly dynamic rather than a combination of dynamic and static.
We are currently resorting to automatically clearing manual changes to vital smart groups via a daily cron job which is a little silly.
2. Having to check for manually added/removed contacts significantly slows down db queries.
Monitoring mysql, we often see queries like this hogging resources:
SELECT contact_a.id as contact_id FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact `civicrm_group_contact-1580` ON (contact_a.id = `civicrm_group_contact-1580`.contact_id AND `civicrm_group_contact-1580`.status IN ("Added")) LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_1580` ON contact_a.id = `civicrm_group_contact_cache_1580`.contact_id WHERE ( ( ( `civicrm_group_contact-1580`.group_id IN ( 1580 ) ) OR ( `civicrm_group_contact_cache_1580`.group_id IN ("1580") ) ) ) GROUP BY contact_a.id
In our case, this query takes about 3.9 seconds to run.
Compare this to a query which only takes into consideration non-static members of a smart group:
SELECT contact_a.id as contact_id FROM civicrm_contact contact_a inner JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_1580` ON contact_a.id = `civicrm_group_contact_cache_1580`.contact_id WHERE ((( `civicrm_group_contact_cache_1580`.group_id IN ("1580")))) GROUP BY contact_a.id
This executes in 0.16 seconds - a 24+ times performance gain!
Of course note that this will only be as significant for civi instances with large number of contacts and group_contacts. In our case that's 240,000 and 850,000 respectively.
All in all, not only do manually added/removed contacts mess up smart groups, they also bog down the database.
Presumably there are CRM users who prefer this smart group behaviour so I think the best solution would be a CRM-wide setting which turns manually adding contacts on or off.
If off, the query above should only do the single inner join rather than the two left joins.