Contribution tab is slow to render for contacts with many contributions (in large database)

We are finding that the contribution tab is extremely slow to render on contacts with a large number of contributions. While we are taking at a different scale db to most instances (many millions of contacts and the very slow to load contacts have > 10,000 donation) from my digging the issues that cause the slowness are not limited to us and the query improvements that work for us are generally applicable.

We are seeing maybe 10 queries that take about 6 seconds each. In each case it is possible to reduce the query to around 0.05 seconds with an index hint. The issue is exacerbated by the fact that several of the slower queries run twice each.

I've pasted all the queries below

  SELECT COUNT(*) as count,
         SUM(total_amount) as amount,
         AVG(total_amount) as average,
         currency
  FROM civicrm_contribution b
  LEFT JOIN civicrm_line_item i ON i.contribution_id = b.id AND i.entity_table = 'civicrm_contribution'  AND i.financial_type_id NOT IN (3,1,4,2)
  WHERE b.contact_id IN (76) AND b.contribution_status_id = 1 AND b.is_test = 0 AND b.receive_date >= 20180101 AND b.receive_date <  20190101
  GROUP BY currency;

  SELECT count( DISTINCT  civicrm_contribution.id ) as rowCount  
  FROM civicrm_contact contact_a 
  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  
  AND (contact_a.is_deleted = 0)    ;

 SELECT COUNT( conts.total_amount ) as total_count,
   SUM(   conts.total_amount ) as total_amount,
   AVG(   conts.total_amount ) as total_avg,
   conts.currency              as currency FROM (
  SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
  civicrm_contribution.currency  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  GROUP BY civicrm_contribution.id
) as conts
GROUP BY currency;

SELECT COUNT( conts.total_amount ) as total_count,
   SUM(   conts.total_amount ) as total_amount,
   AVG(   conts.total_amount ) as total_avg,
   conts.currency              as currency, SUBSTRING_INDEX(GROUP_CONCAT(conts.total_amount
  ORDER BY conts.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount,
  MAX(conts.civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count
  FROM (SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
  civicrm_contribution.currency  
  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 
  GROUP BY currency, civicrm_contribution.total_amount ORDER BY civicrm_contribution_total_amount_count DESC) as conts
  GROUP BY currency;

  SELECT count(*) as count  FROM civicrm_contact contact_a 
  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  
  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) 
   AND civicrm_contribution.contribution_status_id = 1  AND civicrm_contribution.currency = 'AUD';

  SELECT civicrm_contribution.total_amount as median
  FROM civicrm_contact contact_a 
   LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  
  WHERE  ( contact_a.id = '76' )  
  AND (contact_a.is_deleted = 0) 
   AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  
   AND civicrm_contribution.currency = 'AUD'
         ORDER BY median LIMIT 0,1;

         SELECT count(*) as count  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  AND civicrm_contribution.currency = 'NZD';

         SELECT civicrm_contribution.total_amount as median
          FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  AND civicrm_contribution.currency = 'NZD'
         ORDER BY median LIMIT 0,1;

         SELECT count(*) as count  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  AND civicrm_contribution.currency = 'USD';

         SELECT civicrm_contribution.total_amount as median
          FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  AND civicrm_contribution.currency = 'USD'
         ORDER BY median LIMIT 0,2;

   SELECT COUNT( conts.total_amount ) as cancel_count,
   SUM(   conts.total_amount ) as cancel_amount,
   AVG(   conts.total_amount ) as cancel_avg,
   conts.currency              as currency FROM (
  SELECT civicrm_contribution.total_amount, civicrm_contribution.currency  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.cancel_date IS NOT NULL 
  GROUP BY civicrm_contribution.id
) as conts
GROUP BY currency;

 SELECT COUNT( conts.total_amount ) as total_count,
   SUM(   conts.total_amount ) as total_amount,
   AVG(   conts.total_amount ) as total_avg,
   conts.currency              as currency FROM (
  SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
  civicrm_contribution.currency  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  GROUP BY civicrm_contribution.id
) as conts
GROUP BY currency;

  SELECT COUNT( conts.total_amount ) as total_count,
   SUM(   conts.total_amount ) as total_amount,
   AVG(   conts.total_amount ) as total_avg,
   conts.currency              as currency, SUBSTRING_INDEX(GROUP_CONCAT(conts.total_amount
  ORDER BY conts.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount,
  MAX(conts.civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count
  FROM (SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
  civicrm_contribution.currency  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1 
  GROUP BY currency, civicrm_contribution.total_amount ORDER BY civicrm_contribution_total_amount_count DESC) as conts
  GROUP BY currency;

  SELECT count(*) as count  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  AND civicrm_contribution.currency = 'AUD';

  SELECT civicrm_contribution.total_amount as median
          FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  AND civicrm_contribution.currency = 'AUD'
         ORDER BY median LIMIT 0,1;

         SELECT count(*) as count  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  AND civicrm_contribution.currency = 'NZD';

         SELECT civicrm_contribution.total_amount as median
          FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  AND civicrm_contribution.currency = 'NZD'
         ORDER BY median LIMIT 0,1;

         SELECT count(*) as count  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  AND civicrm_contribution.currency = 'USD';

         SELECT civicrm_contribution.total_amount as median
          FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.contribution_status_id = 1  AND civicrm_contribution.currency = 'USD'
         ORDER BY median LIMIT 0,2;
         
20.

SELECT COUNT( conts.total_amount ) as cancel_count,
   SUM(   conts.total_amount ) as cancel_amount,
   AVG(   conts.total_amount ) as cancel_avg,
   conts.currency              as currency FROM (
  SELECT civicrm_contribution.total_amount, civicrm_contribution.currency  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  WHERE  ( contact_a.id = '76' )  AND (contact_a.is_deleted = 0) AND (contact_a.is_deleted = 0) AND civicrm_contribution.cancel_date IS NOT NULL 
  GROUP BY civicrm_contribution.id
) as conts
GROUP BY currency;

  SELECT a.id as `id`, a.contact_id as `contact_id`, a.amount as `amount`, a.currency as `currency`, a.frequency_unit as `frequency_unit`, a.frequency_interval as `frequency_interval`, a.installments as `installments`, a.start_date as `start_date`, a.create_date as `create_date`, a.modified_date as `modified_date`, a.cancel_date as `cancel_date`, a.end_date as `end_date`, a.processor_id as `processor_id`, a.payment_token_id as `payment_token_id`, a.trxn_id as `trxn_id`, a.invoice_id as `invoice_id`, a.contribution_status_id as `contribution_status_id`, a.is_test as `is_test`, a.cycle_day as `cycle_day`, a.next_sched_contribution_date as `next_sched_contribution_date`, a.failure_count as `failure_count`, a.failure_retry_date as `failure_retry_date`, a.auto_renew as `auto_renew`, a.payment_processor_id as `payment_processor_id`, a.financial_type_id as `financial_type_id`, a.payment_instrument_id as `payment_instrument_id`, a.campaign_id as `campaign_id`, a.is_email_receipt as `is_email_receipt`

FROM civicrm_contribution_recur a WHERE (a.contact_id = "76") AND (a.contribution_status_id IN ("3", "10", "7", "1")) ORDER BY a.is_test, a.start_date DESC;

SELECT SQL_CALC_FOUND_ROWS ccs.id, ccs.amount as amount,
       ccs.contribution_id,
       ccs.pcp_id,
       ccs.pcp_display_in_roll,
       ccs.pcp_roll_nickname,
       ccs.pcp_personal_note,
       ccs.soft_credit_type_id,
       sov.label as sct_label,
       cc.receive_date,
       cc.contact_id as contributor_id,
       cc.contribution_status_id as contribution_status_id,
       cov.label as contribution_status,
       cp.title as pcp_title,
       cc.currency,
       contact.display_name as contributor_name,
       cct.name as financial_type
FROM civicrm_contribution_soft ccs
  LEFT JOIN civicrm_contribution cc
        ON ccs.contribution_id = cc.id
  LEFT JOIN civicrm_pcp cp
        ON ccs.pcp_id = cp.id
  LEFT JOIN civicrm_contact contact ON
  ccs.contribution_id = cc.id AND cc.contact_id = contact.id
  LEFT JOIN civicrm_financial_type cct ON cc.financial_type_id = cct.id
  LEFT JOIN civicrm_option_value sov ON sov.option_group_id = %3 AND ccs.soft_credit_type_id = sov.value
  LEFT JOIN civicrm_option_value cov ON cov.option_group_id = %4 AND cc.contribution_status_id = cov.value

  WHERE cc.is_test = %2 AND ccs.contact_id = %1 ORDER BY cc.receive_date DESC ;

  SELECT count( x.id ) count FROM ( 
  SELECT contribution.id AS id
  FROM civicrm_contribution contribution
  LEFT JOIN civicrm_line_item i ON i.contribution_id = contribution.id AND i.entity_table = 'civicrm_contribution'  AND i.financial_type_id NOT IN (3,1,4,2)
  WHERE contribution.is_test = 0 AND contribution.contact_id = 76
   AND contribution.financial_type_id IN (3,1,4,2)
  AND i.id IS NULL UNION 
  SELECT contribution.id
  FROM civicrm_contribution contribution INNER JOIN civicrm_contribution_soft softContribution
  ON ( contribution.id = softContribution.contribution_id )
  WHERE contribution.is_test = 0 AND softContribution.contact_id = 76 ) x;

Edited Nov 20, 2018 by eileen

To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information