From ce4fb799cd1c55fb63ddaf7b40023b83553a43fd Mon Sep 17 00:00:00 2001
From: David Thompson <davet@gnu.org>
Date: Tue, 13 Jan 2015 16:19:03 -0500
Subject: [PATCH] Delete from the group contact cache in chunks of 1000.

---
 CRM/Contact/BAO/GroupContactCache.php | 29 ++++++++++++++++++---------
 1 file changed, 20 insertions(+), 9 deletions(-)

diff --git a/CRM/Contact/BAO/GroupContactCache.php b/CRM/Contact/BAO/GroupContactCache.php
index be50a15fb4..3d612afb8d 100644
--- a/CRM/Contact/BAO/GroupContactCache.php
+++ b/CRM/Contact/BAO/GroupContactCache.php
@@ -296,11 +296,13 @@ WHERE  id IN ( $groupIDs )
 
     $now         = CRM_Utils_Date::getUTCTime();
     $refreshTime = CRM_Utils_Date::getUTCTime($smartGroupCacheTimeout * 60);
+    // HACK: Hardcoded delete limit.
+    $deleteLimit = 1000;
 
     if (!isset($groupID)) {
       if ($smartGroupCacheTimeout == 0) {
         $query = "
-TRUNCATE civicrm_group_contact_cache
+DELETE FROM civicrm_group_contact_cache LIMIT $deleteLimit
 ";
         $update = "
 UPDATE civicrm_group g
@@ -311,9 +313,14 @@ SET    cache_date = null,
       else {
         $query = "
 DELETE     gc
-FROM       civicrm_group_contact_cache gc
-INNER JOIN civicrm_group g ON g.id = gc.group_id
-WHERE      TIMESTAMPDIFF(MINUTE, g.cache_date, $now) >= $smartGroupCacheTimeout
+FROM       civicrm_group_contact_cache AS gc
+INNER JOIN (
+      SELECT id
+      FROM   civicrm_group
+      WHERE  TIMESTAMPDIFF(MINUTE, cache_date, $now) >= $smartGroupCacheTimeout
+      LIMIT  $deleteLimit
+     ) AS g
+ON g.id=gc.group_id
 ";
         $update = "
 UPDATE civicrm_group g
@@ -335,6 +342,7 @@ AND    refresh_date IS NULL
 DELETE     g
 FROM       civicrm_group_contact_cache g
 WHERE      g.group_id IN ( $groupIDs )
+LIMIT      $deleteLimit
 ";
       $update = "
 UPDATE civicrm_group g
@@ -345,9 +353,10 @@ WHERE  id IN ( $groupIDs )
     }
     else {
       $query = "
-DELETE     g
-FROM       civicrm_group_contact_cache g
-WHERE      g.group_id = %1
+DELETE
+FROM       civicrm_group_contact_cache
+WHERE      group_id = %1
+LIMIT      $deleteLimit
 ";
       $update = "
 UPDATE civicrm_group g
@@ -358,7 +367,10 @@ WHERE  id = %1
       $params = array(1 => array($groupID, 'Integer'));
     }
 
-    CRM_Core_DAO::executeQuery($query, $params);
+    // Nibble at the rows, don't try to delete everything at once!
+    do {
+      $dao = CRM_Core_DAO::executeQuery($query, $params);
+    } while($dao->affectedRows() != 0);
 
     if ($refresh) {
       CRM_Core_DAO::executeQuery($refresh, $params);
@@ -623,4 +635,3 @@ ORDER BY   gc.contact_id, g.children
   }
 
 }
-
-- 
2.25.1