Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-17257

Option to disable "dumb" smart groups


    • Documentation Required?:
    • Funding Source:
      Needs Funding


      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.




            • Assignee:
              stan_d Stan Dragnev
            • Votes:
              2 Vote for this issue
              9 Start watching this issue


              • Created: