Using LIMIT with DELETE: Benefits and Best Practices in MySQL

In high‑traffic database scenarios, adding LIMIT 1 to single‑row DELETE or UPDATE statements can avoid unnecessary full‑table scans and improve efficiency.

The author asks whether developers should habitually add LIMIT to DELETE statements and presents a typical example:

delete from t where sex = 1 limit 100;

While TRUNCATE is recommended for clearing whole tables because it bypasses transaction logging and releases disk space, the discussion focuses on DELETE with LIMIT . MySQL supports the syntax DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE ...] [ORDER BY ...] [LIMIT row_count] , and ORDER BY must be used together with LIMIT to avoid optimization removal.

Advantages of using LIMIT include:

Reducing the cost of accidental deletions—if the limit is set too high, only that many rows are lost and recovery via binlog is straightforward.

Preventing long‑running transactions; MySQL places write and gap locks on affected rows, and large deletions can lock the table and impact other operations.

Avoiding CPU saturation when deleting massive amounts of data, which can cause the deletion to become progressively slower.

The benefits assume the filtered column (e.g., sex ) is indexed; otherwise, the operation may still scan the primary key and cause broader locking.

The article presents a MySQL interview question about deleting the first 10,000 rows using three approaches: a single DELETE ... LIMIT 10000 , looping 20 times with DELETE ... LIMIT 500 in one connection, or running 20 concurrent connections each executing DELETE ... LIMIT 500 . Experts discuss trade‑offs, concluding that the second method—serially executing multiple short‑lived transactions—is generally safer, reduces lock time, and avoids excessive lock contention.

Overall, the guidance is to habitually add a LIMIT clause when performing deletions: it makes the operation safer, limits lock scope, and improves performance, especially for large‑scale data removal.