[PHP-WEBMASTER] [web-master] improve-notes-removing-speed: The DELETE with sub-query was *mega* slow, replace with a fast loop

[Derick Rethans]

Author: Derick Rethans (derickr)
Date: 2024-08-27T10:55:35Z

Commit: 
https://github.com/php/web-master/commit/773bbf45d05847aa82f68673a47d4d68efad35d8
Raw diff: 
https://github.com/php/web-master/commit/773bbf45d05847aa82f68673a47d4d68efad35d8.diff

The DELETE with sub-query was *mega* slow, replace with a fast loop

This seems counter intuitive, but without the loop it hadn't done
anything in ten minutes, but with the loop it took 3 seconds to remove
the 1940 notes that were old and sub-standard.

Changed paths:
  M  scripts/remove-low-rated-notes


Diff:

diff --git a/scripts/remove-low-rated-notes b/scripts/remove-low-rated-notes
index 5ece697..ef66988 100755
--- a/scripts/remove-low-rated-notes
+++ b/scripts/remove-low-rated-notes
@@ -23,16 +23,20 @@ $table = "Rating | Note\n"
        . "-------+---------------------------------------------------------\n";
 
 $count = 0;
+$noteIDs = [];
 foreach ($result as $row) {
     $table .= sprintf("%5d  | https://php.net/manual/en/%s.php#%s\n";, 
$row['weight'], $row['sect'], $row['note_id']);
-       $count++;
+    $noteIDs[] = $row['note_id'];
+    $count++;
 }
 
 $body = "Following were the {$count} notes with a rating less than " . 
RATING_THRESHOLD . " and\nare older than " . AGE_THRESHOLD. ".\n\n"
       . sprintf("These notes represented %.1f%% of the %d total user 
notes,\nand have now been removed.\n\n", ($count / $total) * 100, $total)
       . $table;
 
-$query = "DELETE FROM note WHERE id IN (SELECT note.id FROM note, votes WHERE 
note.ts < ? AND note.id = votes.note_id GROUP BY note_id HAVING SUM(if (vote = 
0, -1, 1)) < ?)";
-$result = $pdo->safeQuery($query, [ $date, RATING_THRESHOLD ]);
+foreach ($noteIDs as $noteID) {
+    $query = "DELETE FROM note WHERE id = ?";
+    $result = $pdo->safeQuery($query, [ $noteID ]);
+}
 
 mail("php...@lists.php.net, php-no...@lists.php.net", "Deleted $count old and 
low rated notes", $body, "From: nore...@php.net", "-fnore...@php.net");