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

Option to disable "dumb" smart groups

    Details

    • 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.

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: