Why Adding LIMIT to DELETE Statements Is a Good Practice in MySQL

In high‑traffic database scenarios, appending LIMIT 1 to single‑row DELETE or UPDATE statements can dramatically improve efficiency by stopping the operation after the first matching row, avoiding full table scans.

The article asks whether adding LIMIT should become a routine habit for everyday DELETE statements and introduces a typical example:

delete from t where sex = 1 limit 100;

It notes that while many developers are unfamiliar with this practice, understanding its impact is important.

Before diving into DELETE specifics, the author reminds readers that for clearing whole tables, TRUNCATE is far more efficient because it bypasses transaction logging and releases disk space immediately.

The supported DELETE … LIMIT syntax is described as follows (the ORDER BY clause must accompany LIMIT to avoid being optimized away):

delete [low_priority] [quick] [ignore] from tbl_name
[where ...]
[order by ...]
[limit row_count]

Advantages of using LIMIT with DELETE include:

Limiting the damage of accidental deletions; even if the wrong rows are removed, the loss is bounded (e.g., 500 rows) and can be recovered via binlog.

Preventing long‑running transactions that acquire extensive write and gap locks, which can block other operations.

Avoiding CPU saturation and progressively slower deletions when large data volumes are removed without a limit.

The article emphasizes that these benefits assume the filtered column (e.g., sex ) is indexed; otherwise, even a LIMIT on a non‑indexed column may still cause a full table scan and lock the entire table.

An illustrative MySQL expert question presents three ways to delete the first 10,000 rows of a table:

1) delete from T limit 10000;
2) loop 20 times: delete from T limit 500; (single connection)
3) run 20 concurrent connections each executing delete from T limit 500;

Community responses (Tony Du, “肉山”, and “～嗡嗡”) discuss trade‑offs: a single large transaction holds locks longer, while multiple short transactions reduce lock time and improve concurrency; however, running many connections simultaneously can cause lock contention.

Consensus is that the second approach—looping a moderate LIMIT in a single connection—is generally the safest and most efficient method.

In conclusion, the article recommends habitually adding LIMIT to DELETE statements to control the number of rows removed, make operations safer, and minimize locking and performance issues.