Long running sql queries from CiviCase
We have seen recently several sites with long running sql queries after upgrading to 5.10.3 that seem to relate to civicase.
Found slow query:
Id: 2479160
User: xxxx
Host: localhost
db: xxxxx
Command: Query
Time: 86
State: Copying to tmp table
Info: SELECT COUNT(*) FROM (SELECT civicrm_case.id as case_id, civicrm_case.subject as case_subject, civicrm_contact.id as contact_id, civicrm_contact.sort_name as sort_name, civicrm_phone.phone as phone, civicrm_contact.contact_type as contact_type, civicrm_contact.contact_sub_type as contact_sub_type, t_act.activity_type_id, c_type.title as case_type, civicrm_case.case_type_id as case_type_id, cov_status.label as case_status, cov_status.label as case_status_name, t_act.status_id, civicrm_case.start_date as case_start_date, case_relation_type.label_b_a as case_role, t_act.desired_date as case_activity_date, t_act.id as case_activity_id, t_act.act_type_name as case_activity_type_name, t_act.act_type AS case_activity_type FROM civicrm_case
INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id LEFT JOIN
(
SELECT ca4.case_id, act4.id AS id, act4.activity_date_time AS desired_date, act4.activity_type_id, act4.status_id, aov.name AS act_type_name, aov.label AS act_type
FROM civicrm_activity act4
LEFT JOIN civicrm_case_activity ca4
ON ca4.activity_id = act4.id
AND act4.is_current_revision = 1
LEFT JOIN civicrm_option_group aog
ON aog.name='activity_type'
LEFT JOIN civicrm_option_value aov
ON aov.option_group_id = aog.id
AND aov.value = act4.activity_type_id
) AS t_act
ON t_act.case_id = civicrm_case.id
LEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary=1)
LEFT JOIN civicrm_relationship case_relationship
ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 17662
AND case_relationship.case_id = civicrm_case.id )
LEFT JOIN civicrm_relationship_type case_relation_type
ON ( case_relation_type.id = case_relationship.relationship_type_id
AND case_relation_type.id = case_relationship.relationship_type_id )
LEFT JOIN civicrm_case_type c_type
ON civicrm_case.case_type_id = c_type.id
LEFT JOIN civicrm_option_group cog_status
ON cog_status.name = 'case_status'
LEFT JOIN civicrm_option_value cov_status
ON ( civicrm_case.status_id = cov_status.value
AND cog_status.id = cov_status.option_group_id )
WHERE (1) AND civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1 AND civicrm_case.status_id != 2 GROUP BY case_id ORDER BY case_activity_date ASC ) temp
Looks like the query is generated from CRM_Case_BAO_Case::getCaseActivityQuery(). Any idea what changed in Civicase since 5.4?