Improving performance of retreiving display names for multivalued custom data
.Have been taking a look at https://github.com/civicrm/civicrm-core/pull/20764/files#diff-36ab608da0b5718996afd18c10a8c12653c2a1675e81fd3c4dc3e40c2ebb25d2R1040.
My hunch is that for a query that returns 50,000 rows, the 'group_concat find_in_set' subquery is executed 50,000 times even if pagination is turned on since the limit is applied after it does all the subqueries
If you turn that subquery into a 'superquery', i.e. you do the 'group_concat find_in_set' hydration after the limit, it imrpoves the performance a lot.
I did a proof of concept below. For a query with 500 results it went from 37 seconds to 1.3 seconds.
And for 34,000 it went from never completing to 1.0 seconds.
Not sure how easy it would be to acheive this within the constraints of searchkit @colemanw but it would be great to get your initial thoughts on this.
Proof of concept changes to the query below...
Changing this query:
SELECT `a`.`id`, (
SELECT GROUP_CONCAT(
`display_name`
ORDER BY FIND_IN_SET(`civicrm_contact`.`id`, REPLACE(`Case_of_abuse_details_1`.`perpetrator_143`, '', ','))
SEPARATOR ''
)
FROM `civicrm_contact`
WHERE FIND_IN_SET(`civicrm_contact`.`id`, REPLACE(`Case_of_abuse_details_1`.`perpetrator_143`, '', ','))
) AS `Case_of_abuse_details.Perpetrator.display_name`
FROM civicrm_activity a
LEFT JOIN `civicrm_value_case_of_abuse_68` `Case_of_abuse_details_1` ON a.id = Case_of_abuse_details_1.entity_id
WHERE ((`a`.`activity_type_id` IS NULL OR (`a`.`activity_type_id` IN (1, 2, 3, 4, 5, 9, 12, 13, 14, 15, 16, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 40, 44, 45, 48, 49, 50, 51, 52, 53, 55, 57, 59, 61, 63, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74)))) AND (`a`.`activity_type_id` = "68") AND (`a`.`is_test` = "0") AND (`a`.`is_deleted` = "0")
GROUP BY `a`.`id`
LIMIT 50
OFFSET 0
into this query
SELECT id,(SELECT GROUP_CONCAT(
`display_name`
ORDER BY FIND_IN_SET(`civicrm_contact`.`id`, REPLACE(`perpetrator_143`, '', ','))
SEPARATOR ''
)
FROM `civicrm_contact`
WHERE FIND_IN_SET(`civicrm_contact`.`id`, REPLACE(`perpetrator_143`, '', ','))
) AS `Case_of_abuse_details.Perpetrator.display_name` FROM (SELECT `a`.`id`, `Case_of_abuse_details_1`.`perpetrator_143`
FROM civicrm_activity a
LEFT JOIN `civicrm_value_case_of_abuse_68` `Case_of_abuse_details_1` ON a.id = Case_of_abuse_details_1.entity_id
WHERE ((`a`.`activity_type_id` IS NULL OR (`a`.`activity_type_id` IN (1, 2, 3, 4, 5, 9, 12, 13, 14, 15, 16, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 40, 44, 45, 48, 49, 50, 51, 52, 53, 55, 57, 59, 61, 63, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74)))) AND (`a`.`activity_type_id` = "68") AND (`a`.`is_test` = "0") AND (`a`.`is_deleted` = "0")
GROUP BY `a`.`id`
LIMIT 50
OFFSET 0) AS alias