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