Details
-
Type:
Bug
-
Status: Done/Fixed
-
Priority:
Minor
-
Resolution: Duplicate
-
Affects Version/s: 4.6.8
-
Fix Version/s: None
-
Component/s: CiviCRM Search
-
Labels:
-
Documentation Required?:None
-
Funding Source:Needs Funding
Description
I was investigating slow performance on a client's Civi instance, and traced it to a SQL query that was running without an index (query is pasted below). The LEFT JOIN to civicrm_group is unindexed, which brings the entire server to a halt for a VERY long time (When I killed the query it had run 11710 seconds).
The query is generated whenever you use Search Builder and search on a group - and the culprit is this commit: https://github.com/civicrm/civicrm-core/commit/0cd506235b56c03da23dc835cfd1e50b73296a00
Specifically, this line:
case 'civicrm_group':
$from .= " $side JOIN civicrm_group ON (civicrm_group.id = civicrm_group_contact.group_id OR civicrm_group.id = civicrm_group_contact_cache.group_id) ";
continue;
That OR statement is intended to catch smart groups - but causes this JOIN to be unindexed.
Given that this bug was introduced in the middle of the 4.6 cycle, I think that this commit should be reverted if it can't be fixed, but I'll leave it to the core team to decide how to proceed.
SELECT contact_a.id as contact_id, contact_a.contact_type as
`contact_type`, contact_a.contact_sub_type as `contact_sub_type`,
contact_a.sort_name as `sort_name`,
GROUP_CONCAT(DISTINCT(civicrm_group.title)) as groups,
civicrm_contribution.id as contribution_id,
civicrm_contribution.receive_date as `receive_date`,
civicrm_contribution.thankyou_date as `thankyou_date`,
`civicrm_group_contact-301`.id as group_contact_id,
`civicrm_group_contact-301`.status as status FROM
civicrm_prevnext_cache pnc INNER JOIN civicrm_contact contact_a ON
contact_a.id = pnc.entity_id1 AND pnc.cacheKey = 'civicrm search
4d58b7b8f827200bbec4a1867856cf27_3195' LEFT JOIN civicrm_group_contact
ON civicrm_group_contact.contact_id = contact_a.id AND
civicrm_group_contact.status = 'Added' LEFT JOIN
civicrm_group_contact_cache ON civicrm_group_contact_cache.contact_id =
contact_a.id LEFT JOIN civicrm_group ON (civicrm_group.id =
civicrm_group_contact.group_id OR civicrm_group.id =
civicrm_group_contact_cache.group_id) LEFT JOIN civicrm_contribution ON
civicrm_contribution.contact_id = contact_a.id LEFT JOIN
civicrm_group_contact `civicrm_group_contact-301` ON (contact_a.id =
`civicrm_group_contact-301`.contact_id AND
`civicrm_group_contact-301`.status IN ("Added")) LEFT JOIN
civicrm_group_contact_cache `civicrm_group_contact_cache_301` ON
contact_a.id = `civicrm_group_contact_cache_301`.contact_id WHERE (
( ( ( `civicrm_group_contact-301`.group_id != 301 ) OR (
`civicrm_group_contact_cache_301`.group_id != 301 ) ) AND
civicrm_contribution.receive_date > 20150630 AND
(NULLIF(civicrm_contribution.thankyou_date, '') IS NULL) ) ) AND
(contact_a.is_deleted = 0) GROUP BY contact_a.id ORDER BY pnc.id
LIMIT 0, 50