Performance issue with advanced search result count when searching on several groups and one or more is a Smart group
In advanced search we are searching for all users in any one of 3 groups. One of those groups is a fairly large smart group. One of the slow queries observed and checked in the slow query log (over 60 seconds) is the one for getting the total number of results.
SELECT count(DISTINCT contact_a.id) as rowCount FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact civicrm_group_contact-2032325,930575 ON (contact_a.id = civicrm_group_contact-2032325,930575.contact_id AND civicrm_group_contact-2032325,930575.status IN ('Added')) LEFT JOIN civicrm_group_contact_cache civicrm_group_contact_cache_2031143 ON contact_a.id = civicrm_group_contact_cache_2031143.contact_id WHERE ( ( ( civicrm_group_contact-2032325,930575.group_id IN ( 2032325,930575 ) ) OR ( civicrm_group_contact_cache_2031143.group_id IN ("2031143") ) ) ) AND (contact_a.is_deleted = 0);
MySQL EXPLAIN gives us a query_cost for this of 5505518.60
I came up with an alternate query at a much lower cost but I don't know enough about the Contact Query code to try it out and test it.
SELECT count(DISTINCT c.id) as rowCount FROM civicrm_contact c LEFT JOIN ( select group_id, contact_id from civicrm_group_contact_cache WHERE group_id IN (2031143) UNION select group_id, contact_id from civicrm_group_contact WHERE group_id IN (2032325,930575) AND status IN ('Added') ) G ON G.contact_id = c.id WHERE c.is_deleted = 0 and G.group_id IS NOT NULL
MySQL EXPLAIN gives us a query_cost for this one of only 102510.43