Lybunt performance improvement
A couple of years back I did some work on the performance of the Lybunt report https://issues.civicrm.org/jira/browse/CRM-17837 which succeeded in improving the query enough that it would run for prior years there is still an exponential inefficiency in the query which means that 2 years later & some large number of donations later we are back to it not running.
The current query is
CREATE TEMPORARY TABLE civicrm_tmp_e_rptlybunt_20181114_5beb5dc8d8e46 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as cid FROM civicrm_tmp_e_rptgrp_20181114_5beb5dc8d1b4b group_temp_table INNER JOIN civicrm_contact contact_civireport
ON group_temp_table.id = contact_civireport.id INNER JOIN civicrm_contribution contribution_civireport ON contribution_civireport.contact_id = contact_civireport.id
AND contribution_civireport.is_test = 0
AND contribution_civireport.receive_date BETWEEN '20170101000000' AND '20171231235959'
LEFT JOIN civicrm_contribution cont_exclude ON cont_exclude.contact_id = contact_civireport.id
AND cont_exclude.receive_date BETWEEN '2018-01-01' AND '20181231235959' WHERE cont_exclude.id IS NULL AND 1 AND 1
GROUP BY contact_civireport.id;
and it takes 394 seconds
to return only a few hundred rows (the group has only 730 contacts).
Playing around I was able to reduce this time to .24 second by re-writing the query as
SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as cid
FROM civicrm_tmp_d_dflt_3b5e17ad9138b8cc56282f75b2967e9e group_temp_table INNER JOIN civicrm_contact contact_civireport
ON group_temp_table.id = contact_civireport.id
WHERE group_temp_table.id IN
(
SELECT group_temp_table.id FROM civicrm_tmp_d_dflt_3b5e17ad9138b8cc56282f75b2967e9e group_temp_table
INNER JOIN civicrm_contribution contribution_civireport ON contribution_civireport.contact_id = group_temp_table.id
AND contribution_civireport.is_test = 0
AND contribution_civireport.receive_date BETWEEN '20170101000000' AND '20171231235959'
)
AND group_temp_table.id IN
(
SELECT group_temp_table.id FROM civicrm_tmp_d_dflt_3b5e17ad9138b8cc56282f75b2967e9e group_temp_table LEFT JOIN
civicrm_contribution cont_exclude ON cont_exclude.contact_id = group_temp_table.id
AND cont_exclude.receive_date BETWEEN '2018-01-01' AND '20181231235959'
WHERE cont_exclude.id IS NULL
)
GROUP BY contact_civireport.id;
I experimented on our staging site on running the query on our WHOLE data base (ie. without the group constraint) and it completed in 720 seconds - which is outrageously good really since that legitimately queries a LOT of records.
I'm going to look at how to fix up the LYBUNT report to use the above query. There are already some tests from last time I worked on performance on this report.