How to delete lots of rows from a MySQL database without indefinite locking

Interesting post over at the Flare blog, where they had to delete over 900 million records from a MySQL table. They couldn’t simply run a DELETE FROM table query, as that would lock it:

When a deletion query using a WHERE clause starts, it will lock that table. All other queries that are run against the table will have to wait.

Because of the massive size of the table, the deletion query would probably run for hours. The lock would be in place for hours, essentially halting the entire app.

What I learnt from the post is that you can add a LIMIT clause to a DELETE query.

# Will only delete the first 1000 rows that match
DELETE FROM `events`
WHERE `added_on` < "2020-12-13 23:00:00"
LIMIT 1000;

That way you can chunk your DELETEs, so that your table isn’t locked for an indefinite amount of time. Here’s a little script that I built to run locally:

<?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 `events` 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;

In their blog post they mention other ways to do so, including Laravel’s built-in jobs mechanism.

How to safely delete records in massive tables on AWS using Laravel →

Disable all Keyboard and Trackpad Input with Little Fingers

If you have a toddler running around, Little Fingers by Shaun Inman – who created it for his own need – will come in handy.

Little fingers want to explore. Press Shift + Control + Option + Command + L to lock down your Keyboard, Trackpad, Touch Bar, and/or Mouse.

Press it again to unlock.

The source is available on GitHub.

Little Fingers →
Little Fingers Source (GitHub) →

Related: AlphaBaby also comes in handy when having babies/toddlers around 🙂

AlphaBaby

My kids love to play with the computer. Unfortunately, they also have the habit of renaming my hard drive to “axlfkaj”. So, I wrote AlphaBaby [which] lets even infants and toddlers have fun playing with the computer: Every time a key is pressed or the mouse is clicked, a letter or shape is drawn [on screen]

Awesome app to keep my one year old boy from doing less awesome stuff on my Mac 🙂

AlphaBaby →