Case activity filter causes performance headache for non-superusers
I received a report that contacts were taking over 60 seconds to load. This db has ~175,000 contacts and ~272,000 activities. It does not use CiviCase.
I traced the problem to the calculation of the activity count to display on the activity tab. This is the query that's taking ~71 seconds:
SELECT count(*) as c
FROM civicrm_activity a
LEFT JOIN civicrm_case_activity actjoin_case_id ON (actjoin_case_id.activity_id = a.id)
WHERE (((`a`.`activity_type_id` IN (1, 2, 3, 4, 5, 6, 9, 10, 11, 12, 19, 22, 34, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 67, 68, 69, 70, 71, 72, 73, 74))))
AND (((`a`.`id` IN (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id IN (SELECT contact_id FROM civicrm_acl_contact_cache WHERE user_id = 148762)))))
AND (a.id IN (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = "136672"))
AND (actjoin_case_id.case_id IS NULL)
AND (a.is_current_revision = "1")
AND (a.activity_type_id IN ("1", "58", "59", "60", "61", "62", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31",
"32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "63", "64", "67", "68", "69", "70", "71", "72", "73", "74", "75"))
AND (a.is_test = "0")
AND (a.is_deleted = "0")
I removed the references to CiviCase and it went down to .25 seconds:
SELECT count(*) as c
FROM civicrm_activity a
WHERE (((`a`.`activity_type_id` IN (1, 2, 3, 4, 5, 6, 9, 10, 11, 12, 19, 22, 34, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 67, 68, 69, 70, 71, 72, 73, 74))))
AND (((`a`.`id` IN (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id IN (SELECT contact_id FROM civicrm_acl_contact_cache WHERE user_id = 148762)))))
AND (a.id IN (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = "136672"))
AND (a.is_current_revision = "1")
AND (a.activity_type_id IN ("1", "58", "59", "60", "61", "62", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31",
"32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "63", "64", "67", "68", "69", "70", "71", "72", "73", "74", "75"))
AND (a.is_test = "0")
AND (a.is_deleted = "0")
This site went from 5.65 to 5.67 a day ago, and was updated to PHP 8.1.26 from some pre-8.1.25 version.
I don't know if anyone has insight into this, but I don't think I can move the CiviCase-specific code to an extension because a) this is API3 and b) there's no hook. I can at least make this not try to filter by CiviCase when CiviCase is disabled.