Delete a bunch of rows from MySQL database without table locking

Running a DELETE FROM table query in a database with millions of records would lock the table and cause the website to halt. This is where LIMIT clause comes in handy.

The following query will only delete the first 1000 rows that match:

DELETE FROM `table_with_too_many_records`
WHERE `added_on` < "2020-12-13 23:00:00"
LIMIT 1000;

Here is a little PHP scripts that performs that query:

<?php

$cutoffDate = new \DateTime('-1 month');
$batchSize = 1000;

$db = …;

echo "♺ Deleting events before " . $cutoffDate->format(\DateTime::RFC3339) . PHP_EOL;
$total = 0;
do {
    $res = $db-> executeStatement('DELETE FROM `table_with_too_many_records` WHERE `added_on` < ? LIMIT ' . $batchSize . ';', [ $cutoffDate->format('U') ]);
    echo "  - [" . date(\DateTime::RFC3339) . "] Deleted " . $res . " rows" . PHP_EOL;
    $total += $res;
} while ($res == $batchSize);

echo "\x07✔ Deleted a total of $total rows" . PHP_EOL . PHP_EOL;

and an artisan command:

namespace App\Console\Commands;

use App\Domain\Error\Models\ErrorOccurrence;
use Illuminate\Console\Command;

class CleanTableCommand extends Command
{
    protected $signature = 'clean-table';

    protected $description = 'Clean up the table';

    public function handle()
    {
        $this->info('Cleaning table...')
    
        MyModel::query()
            ->where('created_at', '<', now()->subMonth())
            ->limit(1000)
            ->delete();
            
        $this->info('All done!');
    }
}