Why Adding LIMIT to MySQL DELETE Is a Must: Best Practices & Pitfalls

In high‑performance database scenarios, adding LIMIT 1 to DELETE or UPDATE statements is a good habit because it can stop a full table scan after the first matching row, dramatically improving efficiency.

When regularly executing DELETE, should we always add LIMIT?

Typical usage example: delete from t where sex = 1 limit 100; If you need to clear a table, use TRUNCATE instead of DELETE because TRUNCATE is non‑transactional, does not lock the table, generates minimal logs, releases disk space immediately, and resets auto_increment. DELETE does not free disk space and may leave gaps.

Focusing on DELETE only, MySQL supports the LIMIT keyword with a single numeric argument ( LIMIT row_count). Note that ORDER BY must be used together with LIMIT, otherwise the optimizer may drop it.

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
  [WHERE ...]
  [ORDER BY ...]
  [LIMIT row_count];

Advantages of Adding LIMIT

Reduces the cost of a mistaken delete; if you delete the wrong rows, only the limited number is lost and can be recovered via binlog.

Avoids long‑running transactions; MySQL locks all affected rows and gaps, so large deletions can block other operations.

Prevents CPU saturation on massive deletes, which would otherwise slow down progressively.

If the sex column is indexed, locking is based on the index; without an index, even a single matching row can cause a full‑table lock.

If you need to delete the first 10,000 rows of a table, which method is best? 1) DELETE FROM T LIMIT 10000 2) In a single connection, loop 20 times: DELETE FROM T LIMIT 500 3) In 20 connections simultaneously: DELETE FROM T LIMIT 500

Opinions from the community:

Tony Du : Method 1 holds locks longer; Method 2 splits the work into shorter transactions, reducing lock time and improving concurrency; Method 3 creates unnecessary lock contention. Method 2 is generally preferable.

肉山 : Method 1 locks for a long time; Method 2 allows other clients to work between short transactions; Method 3 increases lock competition. Choose based on table size, concurrency, and business impact.

～嗡嗡 : Directly deleting 10,000 rows may cause a long transaction; looping deletes 500 rows each time is safer and faster; simultaneous deletes risk deadlocks.

Most practitioners favor the second approach—looping DELETE FROM T LIMIT 500 in a single connection—because it balances safety and performance.

In summary, appending LIMIT to DELETE statements is a recommended habit: it controls the number of rows removed, makes the operation safer, and reduces lock scope, leading to more efficient and reliable data deletion.