1

I am trying to delete about 2.2 million rows by a column called entity_id from a table called url_rewrites. There could be multiple rows with the same entity_id. I got a file that I run which contains a row for each entity_id, like this:

delete from url_rewrite where entity_id = 2128693;
...
...

Now, running this script takes a long time and the website that belongs to the DB is used of course. So every 5 or 10 minutes and sometimes faster I get a deadlock and the scripts stops so I have to restart it manually.

I tried adding locking the table for WRITE access with LOCK TABLES url_rewrite WRITE; in the beginning of the script, but this stops the website from working correctly (it wont respond).

Is there any other way to run the script or delete those rows blocking the website?

Thanks!

Arnie
  • 661
  • 9
  • 20
  • 1
    look this URl - https://stackoverflow.com/questions/1318972/deleting-millions-of-rows-in-mysql may this helps you – Kandy Oct 17 '19 at 05:40
  • Is there any chance you could take the site down for 15-20 minutes at a time when you don't expect many people to be using it? – Tim Biegeleisen Oct 17 '19 at 05:40
  • @Tim Biegeleisen: Taking it down for 20 minutes only deletes some of the rows because the table is so huge. I would have to do it 100000 times to finish I think. Anyway, maybe at night I could take it down for some time, yes. – Arnie Oct 17 '19 at 05:42
  • @Kandy: **This did the trick!** I used the solution from **user1459144** on your link, took only 10 minutes to get the job done. – Arnie Oct 17 '19 at 06:33

0 Answers0