Introduction

Deleting many rows in MySQL by id is easy to write but risky at scale if done without batching and safety checks. Large deletes can lock rows for long periods, grow replication lag, and impact application latency. The best approach depends on row volume, table size, and whether ids come from a list, a subquery, or a staging table.

Delete by Explicit ID List

For small batches, IN is straightforward and readable.

DELETE FROM orders
WHERE id IN (101, 145, 209, 310);

Always preview first:

SELECT id FROM orders
WHERE id IN (101, 145, 209, 310);

Run preview and delete in the same deployment window to avoid stale assumptions.

Use Join Delete with a Staging Table

For thousands or millions of ids, load ids into a temporary or staging table and join.

1CREATE TEMPORARY TABLE ids_to_delete (
2  id BIGINT PRIMARY KEY
3);
4
5INSERT INTO ids_to_delete (id) VALUES
6(101), (145), (209), (310);
7
8DELETE o
9FROM orders o
10JOIN ids_to_delete d ON d.id = o.id;

This pattern scales better than very large IN lists and is easier to audit.

Batch Large Deletes to Reduce Lock Pressure

Mass deletes in one transaction can block writes and create long undo logs. Delete in chunks using primary key order.

1-- repeat until affected rows = 0
2DELETE FROM orders
3WHERE id IN (
4  SELECT id FROM (
5    SELECT id
6    FROM orders
7    WHERE created_at < '2024-01-01'
8    ORDER BY id
9    LIMIT 1000
10  ) AS t
11);

The nested subquery avoids MySQL restrictions on modifying and selecting from same table in one query block.

For automation, run chunk loop in application code or scheduled job.

Transaction and Safety Controls

Use transactions carefully. For huge jobs, many small commits are safer than one huge commit.

1START TRANSACTION;
2
3DELETE FROM orders WHERE id BETWEEN 5000 AND 5999;
4DELETE FROM orders WHERE id BETWEEN 6000 AND 6999;
5
6COMMIT;

Before deletion:

• verify backup or point in time recovery availability
• confirm foreign key cascade behavior
• ensure WHERE clause is indexed
• run on replica or staging first when possible

Handle Foreign Keys and Child Rows

If child tables reference parent ids, delete order matters unless cascading is configured.

-- child first if no cascade
DELETE FROM order_items WHERE order_id IN (101, 145, 209, 310);
DELETE FROM orders WHERE id IN (101, 145, 209, 310);

If cascade exists, validate it with a controlled test before production execution.

Monitor Impact During Execution

Track these metrics while batch delete runs:

• query latency and lock wait time
• replica lag if using replication
• rows affected per batch
• application error rate

Throttle batch size dynamically if latency or lag crosses safe thresholds.

Common Pitfalls

A common pitfall is forgetting WHERE due to template mistakes. Use guardrails in migration scripts that fail when target count is unexpectedly high.

Another issue is deleting by non indexed condition, causing full table scans and severe lock contention.

A third issue is using one giant delete transaction in peak traffic hours. Prefer chunked deletes during low traffic windows.

Finally, teams often skip post delete validation. Always verify remaining row counts and downstream data integrity after cleanup.

Summary

• Use DELETE ... IN for small known id sets
• For large id lists, stage ids in a table and delete with join
• Batch deletes to reduce lock time and replication impact
• Validate foreign key behavior and indexing before execution
• Monitor runtime metrics and verify integrity after completion