From a5e14883193e5c4d362420e3710ed1cc942df3b3 Mon Sep 17 00:00:00 2001 From: Matthias Schmidt Date: Sat, 22 Jun 2019 14:09:39 +0200 Subject: [PATCH] Delete database objects in batches --- .../lib/data/DatabaseObjectEditor.class.php | 20 +++++++++++++------ 1 file changed, 14 insertions(+), 6 deletions(-) diff --git a/wcfsetup/install/files/lib/data/DatabaseObjectEditor.class.php b/wcfsetup/install/files/lib/data/DatabaseObjectEditor.class.php index 458b822807..1dfa9c0ef6 100644 --- a/wcfsetup/install/files/lib/data/DatabaseObjectEditor.class.php +++ b/wcfsetup/install/files/lib/data/DatabaseObjectEditor.class.php @@ -100,14 +100,22 @@ abstract class DatabaseObjectEditor extends DatabaseObjectDecorator implements I * @inheritDoc */ public static function deleteAll(array $objectIDs = []) { - $sql = "DELETE FROM ".static::getDatabaseTableName()." - WHERE ".static::getDatabaseTableIndexName()." = ?"; - $statement = WCF::getDB()->prepareStatement($sql); - $affectedCount = 0; + + // instead of executing one query per object id, execute queries + // for batches of up to 1000 object ids at once + $itemsPerLoop = 1000; + $batchCount = ceil(count($objectIDs) / $itemsPerLoop); + WCF::getDB()->beginTransaction(); - foreach ($objectIDs as $objectID) { - $statement->execute([$objectID]); + for ($i = 0; $i < $batchCount; $i++) { + $batchObjectIDs = array_slice($objectIDs, $i * $itemsPerLoop, $itemsPerLoop); + + $sql = "DELETE FROM " . static::getDatabaseTableName() . " + WHERE " . static::getDatabaseTableIndexName() . + " IN (?" . str_repeat(', ?', count($batchObjectIDs) - 1) . ")"; + $statement = WCF::getDB()->prepareStatement($sql); + $statement->execute($batchObjectIDs); $affectedCount += $statement->getAffectedRows(); } WCF::getDB()->commitTransaction(); -- 2.20.1