MYSQL delete all data from table which have millions record with same name

Question

Hi i want to delete all record of a table which have 10 millions record but its hang and give me follow error:

Lock wait timeout exceeded; try restarting transaction

I am using the following query:

delete from table where name = '' order by id limit 1000 

in for loop.

Please suggest me how to optimize it.

Answer 1

You said i want to delete all record of a table which have 10 millions record. Then why not use TRUNCATE command instead which will have minimal/no overhead of logging.

TRUNCATE TABLE tbl_name

You can as well use DELETE statement but in your case the condition checking (where name = '' order by id limit 1000) is not necessary since you wanted to get rid of all rows but DELETE has overhead of logging in transaction log which may matter for record volume of millions.

Per your comment, you have no other option rather than going by delete from table1 where name = 'naresh'. You can delete in chunks using the LIMIT operator like delete from table1 where name = 'naresh' limit 1000. So if name='naresh' matches 25000 rows, it will be deleting only 1000 rows out of them.

You can include the same in a loop as well like below (Not tested, minor tweak might require)

 DECLARE v1 INT;

 SELECT count(*) INTO v1 FROM table1 WHERE name = 'naresh';

  WHILE v1 > 0 DO
  DELETE FROM table1 WHERE name = 'naresh' LIMIT 1000;
    SET v1 = v1 - 1000;
  END WHILE;

So in the above code, loop will run for 25 times deleting 1000 rows each time (assuming name='naresh' condition returns 25K rows).

Answer 2

If you want to delete all records(empty table), You can use

TRUNCATE TABLE `table_name_here`...

May be it will work for you... (not tried with big database)