-1

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.

Naresh
  • 632
  • 4
  • 19

2 Answers2

3

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).

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • thanks for reply but i want do delete all record on the basis of name means where name='Rahul' – Naresh Jun 14 '14 at 09:44
  • hi have seen the following link so i use this :http://stackoverflow.com/questions/1318972/deleting-millions-of-rows-in-mysql – Naresh Jun 14 '14 at 09:49
  • @naresh, then you have no other option rather than going by `delete from table1 where name = 'naresh'` – Rahul Jun 14 '14 at 09:49
  • I know that but can you tell me how to get rid of this problem – Naresh Jun 14 '14 at 09:51
  • @naresh, since you are trying to delete all rows matching a particular name; I don't think you really need `order by id limit 1000`. Remove that and just make it `delete from table1 where name = 'naresh'`. See how it goes. – Rahul Jun 14 '14 at 09:52
  • I am try this which you said that but its also hang the DB – Naresh Jun 14 '14 at 10:01
  • @naresh, one last suggestion I could give is: 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. – Rahul Jun 14 '14 at 10:08
0

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)

Shail
  • 873
  • 4
  • 16
  • thanks for reply but i want do delete all record on the basis of name means where name='Shail' – Naresh Jun 14 '14 at 09:45
  • You can use cron job Which will run every 1min(whatever time you set) and delete 100(any no of records) records every time.... – Shail Jun 14 '14 at 09:49
  • @naresh So why does your question say something different? And your title say some different again? – user207421 Jun 14 '14 at 10:46