Oct 10, 2018

How to delete many rows from a large table in MySQL

If you want to delete many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:
  • Select the rows not to be deleted into an empty table that has the same structure as the original table:
create table t_copy like t; insert into t_copy SELECT * FROM t WHERE ... ;
See also:


  • Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:
RENAME TABLE t TO t_old, t_copy TO t;
  • Drop the original table:

DROP TABLE t_old;
Note that Foreign keys that point to the renamed table are not automatically updated. In such cases, you must drop and re-create the foreign keys in order for them to function properly.

See also:

How to get all foreign keys to a table or column in MySQL

No other sessions can access the tables involved while RENAME TABLE executes, so the rename operation is not subject to concurrency problems.
Another way to remove many rows from a large table is by doing something similar to the following:

If you don't need to join tables, you can use the LIMIT row_count option to DELETE to tell the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a given DELETE statement does not take too much time. You can simply repeat the DELETE statement until the number of affected rows is less than the LIMIT value.
If you need to join tables, you can not use the LIMIT row_count option directly to DELETE. You cannot use ORDER BY or LIMIT in a multiple-table DELETE. Then we can turn to a temp table to do that:
CREATE PROCEDURE `new_procedure` ()
BEGIN

create temporary table tmp (id int, index using btree(id)) engine = memory;
truncate tmp;
repeat
insert into tmp select id from big_table where some_condition limit 8888;
delete low_priority s, tmp from big_table s join tmp on tmp.id=s.id;
until ROW_COUNT()=0
end repeat;

END

Another way is to delete one row at one time without using temporary table

repeat
select id into @id from big_table where some_condition limit 1;
delete b from big_table b where b.id=@id;
until row_count()=0
end repeat;
You can use sub-query to do that too. In general, you cannot modify a table and select from the same table in a subquery. But you can if you are using a subquery for the modified table in the FROM clause.

delete t  from transit.tmp t join ( select t.contactid FROM transit.tmp t limit 8) t2 on t.contactid=t2.contactid ;
You can compare them and choose the suitable one.