Cover Image by MilesAndryPrower on DeviantArt
Cover Image by MilesAndryPrower on DeviantArt

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!');
    }
}
whoami
Stefan Pejcic
Join the discussion

I enjoy constructive responses and professional comments to my posts, and invite anyone to comment or link to my site.