How to bulk delete a range of records in MySQL?

Working with bulky datasets

When dealing with large datasets (think more than 60,000 rows), you'll want to streamline your delete operations. Here, consider batching your deletes to lessen the blow to performance:

# Think of it as portion control for your database
DELETE FROM your_table WHERE id BETWEEN ? AND ? LIMIT 1000;


Repeat with your range values until there's nothing left of the records. A bit tedious, but it sure beats sluggish performance.

Don't play Russian Roulette with data

Prior to jumping in, take a peek at the records headed for the chopping block. Use a SELECT statement with the same conditions to reveal the affected records:

# Ghost of future deletion
SELECT * FROM your_table WHERE id BETWEEN 10 AND 20;


After verifying the casualties, you may go on with the DELETE statement.

Backup isn't just for singers

Preserve the peace of mind and your data by backing up before anyone yells "timber":

# Clone wars
CREATE TABLE your_table_backup AS SELECT * FROM your_table;


A duplicate table will be available for data restoration - just in case.

Make deletions run like Usain Bolt

Ensure the deletion crusade doesn't harm database performance:

• Indices: A speed booster for locating records for deletion.
• Off-peak hours: Schedule deletions when usage is low.
• EXPLAIN DELETE ...: Have MySQL spill the beans on how your query runs.

Protecting your data like Gollum

Do not forget transactional safety. Rollback on demand:

# Oops! Safety net deployed
START TRANSACTION;
DELETE FROM your_table WHERE id BETWEEN 10 AND 20;
ROLLBACK; -- revert changes if something goes wrong


For the long haul, track key changes and rollbacks using version control.

Some special cases, because life loves those

When tables form bonds

When you have multiple tables tangled up, use a DELETE with a JOIN:

# It's complicated
DELETE your_table
FROM your_table
JOIN other_table ON your_table.foreign_key = other_table.id
WHERE other_table.condition = value;


The plot thickens: subqueries

For intricate conditions, bring in a subquery fit for a detective story:

# The plot thickens
DELETE FROM your_table
WHERE id IN (
    SELECT id FROM your_table WHERE condition = value
);


Tiptoeing around foreign key constraints

Navigating through dependent records? Handle with care or temporarily turn off the stern foreign key constraint:

# The leash is off
SET FOREIGN_KEY_CHECKS=0; 
DELETE FROM your_table WHERE id BETWEEN 10 AND 20;
SET FOREIGN_KEY_CHECKS=1; 
