0

I ran into the following problematic queries while troubleshooting CiviCRM performance issues.

insert into civicrm_prevnext_cache (cachekey, entity_id1, data)
SELECT DISTINCT 'civicrm search aca2df90f6c92262d16a93ba87cfb617_2667', contact_a.id, contact_a.sort_name
FROM civicrm_contact AS contact_a
JOIN civicrm_case_contact AS ccc
   ON contact_a.id = ccc.contact_id
JOIN civicrm_case AS cas
    ON ccc.case_id = cas.id
        AND cas.is_deleted = 0
LEFT JOIN civicrm_value_nihr_volunteer_general_observations AS nvgo
    ON ccc.contact_id = nvgo.entity_id
LEFT JOIN civicrm_address AS adr
    ON contact_a.id = adr.contact_id
        AND adr.is_primary = 1
LEFT JOIN civicrm_value_nbr_participation_data AS nvpd
    ON cas.id = nvpd.entity_id
LEFT JOIN civicrm_value_nihr_volunteer_ids AS nvi
    ON contact_a.id = nvi.entity_id
LEFT JOIN civicrm_option_value AS genderov
    ON contact_a.gender_id = genderov.value
        AND genderov.option_group_id = ?
LEFT JOIN civicrm_option_value AS ethnicov
    ON nvgo.nvgo_ethnicity_id = ethnicov.value
        AND ethnicov.option_group_id = ?
LEFT JOIN civicrm_option_value AS stustatus
    ON nvpd.nvpd_study_participation_status = stustatus.value
        AND stustatus.option_group_id = ?
WHERE contact_a.contact_sub_type = '?'
        AND nvpd.nvpd_study_id = ?
        AND (nvpd.nvpd_study_participation_status = '?')
ORDER BY  `sort_name` ASC limit 0, 500;

The fix requires an index change and and a query modification:

ALTER TABLE civicrm_contact DROP INDEX index_contact_sub_type, ADD INDEX index_contact_sub_type (contact_sub_type, sort_name);
insert into civicrm_prevnext_cache (cachekey, entity_id1, data)
SELECT 'civicrm search aca2df90f6c92262d16a93ba87cfb617_2667', contact_a.id, contact_a.sort_name
FROM civicrm_contact AS contact_a
JOIN civicrm_case_contact AS ccc
   ON contact_a.id = ccc.contact_id
JOIN civicrm_case AS cas
    ON ccc.case_id = cas.id
        AND cas.is_deleted = 0
LEFT JOIN civicrm_value_nihr_volunteer_general_observations AS nvgo
    ON ccc.contact_id = nvgo.entity_id
LEFT JOIN civicrm_address AS adr
    ON contact_a.id = adr.contact_id
        AND adr.is_primary = 1
LEFT JOIN civicrm_value_nbr_participation_data AS nvpd
    ON cas.id = nvpd.entity_id
LEFT JOIN civicrm_value_nihr_volunteer_ids AS nvi
    ON contact_a.id = nvi.entity_id
LEFT JOIN civicrm_option_value AS genderov
    ON contact_a.gender_id = genderov.value
        AND genderov.option_group_id = 3
LEFT JOIN civicrm_option_value AS ethnicov
    ON nvgo.nvgo_ethnicity_id = ethnicov.value
        AND ethnicov.option_group_id = ?
LEFT JOIN civicrm_option_value AS stustatus
    ON nvpd.nvpd_study_participation_status = stustatus.value
        AND stustatus.option_group_id = ?
WHERE contact_a.contact_sub_type = '?'
        AND nvpd.nvpd_study_id = ?
        AND (nvpd.nvpd_study_participation_status = '?')
GROUP BY contact_a.sort_name, contact_a.id
ORDER BY  `sort_name` ASC limit 0, 500;

Doing a GROUP BY achieves the same thing as the DISTINCT, but it avoids a secondary sorting pass. This gets it from 14 seconds to 300ms.

This one is similar and requires a similar fix:

SELECT distinct(contact_a.id) AS contact_id,
        cas.id AS case_id,
         contact_a.sort_name,
         contact_a.birth_date,
         genderov.label AS gender,
         ethnicov.label AS ethnicity,
         adr.city AS volunteer_address,
         nvpd.nvpd_eligible_status_id,
         nvpd.nvpd_study_participant_id,
         nvpd.nvpd_recall_group,
         stustatus.label AS study_status,
         nvpd.nvpd_date_invited,
         nvpd.nvpd_distance_volunteer_to_study_centre,
         '' AS date_researcher, '' AS latest_visit_date, '' AS volunteer_tags, nvi.nva_participant_id AS participant_id, nvi.nva_bioresource_id AS bioresource_id
FROM civicrm_contact AS contact_a
JOIN civicrm_case_contact AS ccc
    ON contact_a.id = ccc.contact_id
JOIN civicrm_case AS cas
    ON ccc.case_id = cas.id
        AND cas.is_deleted = 0
LEFT JOIN civicrm_value_nihr_volunteer_general_observations AS nvgo
    ON ccc.contact_id = nvgo.entity_id
LEFT JOIN civicrm_address AS adr
    ON contact_a.id = adr.contact_id
        AND adr.is_primary = 1
LEFT JOIN civicrm_value_nbr_participation_data AS nvpd
    ON cas.id = nvpd.entity_id
LEFT JOIN civicrm_value_nihr_volunteer_ids AS nvi
    ON contact_a.id = nvi.entity_id
LEFT JOIN civicrm_option_value AS genderov
    ON contact_a.gender_id = genderov.value
        AND genderov.option_group_id = 3
LEFT JOIN civicrm_option_value AS ethnicov
    ON nvgo.nvgo_ethnicity_id = ethnicov.value
        AND ethnicov.option_group_id = ?
LEFT JOIN civicrm_option_value AS stustatus
    ON nvpd.nvpd_study_participation_status = stustatus.value
        AND stustatus.option_group_id = ?
WHERE contact_a.contact_sub_type = '?'
        AND nvpd.nvpd_study_id = ?
        AND (nvpd.nvpd_study_participation_status = '?')
ORDER BY  `sort_name` ASC limit 0, 100;

Same index modification as above. Similar query structure change to get it from 20s down to 200ms:

SELECT contact_a.id AS contact_id,
        cas.id AS case_id, contact_a.sort_name, contact_a.birth_date,
         genderov.label AS gender, ethnicov.label AS ethnicity, adr.city AS volunteer_address,
         nvpd.nvpd_eligible_status_id, nvpd.nvpd_study_participant_id, nvpd.nvpd_recall_group,
         stustatus.label AS study_status,
         nvpd.nvpd_date_invited,
         nvpd.nvpd_distance_volunteer_to_study_centre,
         '' AS date_researcher, '' AS latest_visit_date, '' AS volunteer_tags, nvi.nva_participant_id AS participant_id, nvi.nva_bioresource_id AS bioresource_id
FROM civicrm_contact AS contact_a
JOIN civicrm_case_contact AS ccc
    ON contact_a.id = ccc.contact_id
JOIN civicrm_case AS cas
    ON ccc.case_id = cas.id
        AND cas.is_deleted = 0
LEFT JOIN civicrm_value_nihr_volunteer_general_observations AS nvgo
    ON ccc.contact_id = nvgo.entity_id
LEFT JOIN civicrm_address AS adr
    ON contact_a.id = adr.contact_id
        AND adr.is_primary = 1
LEFT JOIN civicrm_value_nbr_participation_data AS nvpd
    ON cas.id = nvpd.entity_id
LEFT JOIN civicrm_value_nihr_volunteer_ids AS nvi
    ON contact_a.id = nvi.entity_id
LEFT JOIN civicrm_option_value AS genderov
    ON contact_a.gender_id = genderov.value
        AND genderov.option_group_id = 3
LEFT JOIN civicrm_option_value AS ethnicov
    ON nvgo.nvgo_ethnicity_id = ethnicov.value
        AND ethnicov.option_group_id = ?
LEFT JOIN civicrm_option_value AS stustatus
    ON nvpd.nvpd_study_participation_status = stustatus.value
        AND stustatus.option_group_id = ?
WHERE contact_a.contact_sub_type = '?'
        AND nvpd.nvpd_study_id = ?
        AND (nvpd.nvpd_study_participation_status = '?')
GROUP BY contact_a.sort_name, contact_a.id
ORDER BY  `sort_name` ASC limit 0, 100;

This one is also related and takes 13 seconds:

SELECT count(distinct contact_a.id) AS total
FROM civicrm_contact AS contact_a
JOIN civicrm_case_contact AS ccc
   ON contact_a.id = ccc.contact_id
JOIN civicrm_case AS cas
    ON ccc.case_id = cas.id
        AND cas.is_deleted = 0
LEFT JOIN civicrm_value_nihr_volunteer_general_observations AS nvgo
    ON ccc.contact_id = nvgo.entity_id
LEFT JOIN civicrm_address AS adr
    ON contact_a.id = adr.contact_id
        AND adr.is_primary = 1
LEFT JOIN civicrm_value_nbr_participation_data AS nvpd
    ON cas.id = nvpd.entity_id
LEFT JOIN civicrm_value_nihr_volunteer_ids AS nvi
    ON contact_a.id = nvi.entity_id
LEFT JOIN civicrm_option_value AS genderov
    ON contact_a.gender_id = genderov.value
        AND genderov.option_group_id = 3
LEFT JOIN civicrm_option_value AS ethnicov
    ON nvgo.nvgo_ethnicity_id = ethnicov.value
        AND ethnicov.option_group_id = ?
LEFT JOIN civicrm_option_value AS stustatus
    ON nvpd.nvpd_study_participation_status = stustatus.value
        AND stustatus.option_group_id = ?
WHERE contact_a.contact_sub_type = '?'
        AND nvpd.nvpd_study_id = ?
        AND (nvpd.nvpd_study_participation_status = '?');

All of those outer joins are completely superfluous when doing a count for distinct PK values on the primary table. So the query can be reduced to this for exactly the exact same result:

SELECT count(distinct contact_a.id) AS total
FROM civicrm_contact AS contact_a
JOIN civicrm_case_contact AS ccc
   ON contact_a.id = ccc.contact_id
JOIN civicrm_case AS cas
    ON ccc.case_id = cas.id
        AND cas.is_deleted = 0
LEFT JOIN civicrm_value_nbr_participation_data AS nvpd
    ON cas.id = nvpd.entity_id
WHERE contact_a.contact_sub_type = '?'
        AND nvpd.nvpd_study_id = ?
        AND (nvpd.nvpd_study_participation_status = '?');

This reduced form only takes 1.5 seconds.

I'm told these are a part of Custom Searches from CRM_Contact_Form_Search_Custom_Base.

The cache query above seems to be coming from:

/**
 * @return null|string
 */
public function count() {​​​​​​​​
  return CRM_Core_DAO::singleValueQuery($this->sql('count(distinct contact_a.id) as total'));
}​​​​​​​​

Do these adjustments look like they might be easily implementable upstream? The performance improvements are quite dramatic and in the user-experience path.

1
  • A little additional information: These queries are obviously from a specific extension but use the Custom Search as a basis, and that is where the queries are built. So we think the solutions should be implemented in the core Search classes? May 26 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.