Geek asks and answers

Delete more than 100,000 rows from mysql table - server crashes

My question is, when I try to delete more than 100,000 rows from the mysql table that the server is freezing, and not on its sites you can access more!

I waited 2 hours and then restarted the server and restored the account.

I used the following query:

DELETE FROM `pligg_links` WHERE `link_id` > 10000

-

SELECT* FROM `pligg_links` WHERE `link_id` > 10000

works great

Is there a better way to do this?

+3
source share
3 answers

You can delete rows in smaller sets. A quick script that deletes 1000 lines at a time should see you.

+2
source

Delete from can be very expensive for large datasets.

I recommend using partitioning.

PostgreSQL MySQL, PostgreSQL , "" . . , . , , . , .

:

http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html

+1

Make sure you have the index in the link_id column. And try removing with chunks like 10,000 at a time. Removing from the table is a very expensive operation.

0
source

More articles:


All Articles