From bdb10f0c1e21b7f07d778760202a7439b7f24f4d Mon Sep 17 00:00:00 2001 From: Seamus Lee Date: Sat, 23 Sep 2017 06:25:31 +1000 Subject: [PATCH] Alter Index to be of structure as suggeted by Dave for performance improvement and change the group by on temporary table to be that of membership_id not contact_id so it correctly reports the max receive_date matching the memebership record --- CRM/Report/Form/Member/Detail.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/CRM/Report/Form/Member/Detail.php b/CRM/Report/Form/Member/Detail.php index 9fe46495cd..72c063c159 100644 --- a/CRM/Report/Form/Member/Detail.php +++ b/CRM/Report/Form/Member/Detail.php @@ -505,14 +505,14 @@ class CRM_Report_Form_Member_Detail extends CRM_Report_Form { } $tempTable = CRM_Core_DAO::createTempTableName('civicrm_report_memcontr', TRUE); $sql = " - CREATE TEMPORARY TABLE $tempTable (index (contact_id, receive_date, entity_id)) + CREATE TEMPORARY TABLE $tempTable (INDEX (entity_id, contact_id, receive_date)) SELECT cc.contact_id, max(cc.receive_date) as receive_date, cmp.membership_id as entity_id FROM civicrm_membership_payment cmp INNER JOIN civicrm_contribution cc ON cc.id = cmp.contribution_id WHERE cc.is_test = 0 $dateWhere - GROUP BY cc.contact_id"; + GROUP BY cmp.membership_id"; CRM_Core_DAO::executeQuery($sql); return $tempTable; } -- 2.25.1