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;