plumber-manchester

How to delete thousands of posts in WordPress via PhpMyAdmin

Ok, the question is: how to effectively delete thousands of old wordpress posts without making the server crash?

This is no easy task, given that your website might be running on a shared server with limited resources, and effectively deleting old posts can quickly become heavy.

The solution if you are deleting less than 1000 posts

Well, the best I could find is the WordPress plugin Bulk Delete:

Bulk Delete is a WordPress Plugin which can be used to delete posts in bulk from selected categories, tags or custom taxonomies. This Plugin can also delete all drafts, post revisions, scheduled posts or pages.

This plugin is the solution for you if you are deleting at most 1,000 posts because it internally uses the wordpress default functionality to delete one by one

bulk-delete line 1671:
foreach ($posts as $post) {
wp_delete_post($post->ID, $force_delete);
}

The solution for deleting TENS OF THOUSANDS of posts

This is the point where using the wordpress delete functions begin to fail. If you are on a shared server and try to execute a heavy task the server is going to timeout.

plumber-manchester

The only thing left is get dirty and script the database directly.

This way you can have full control and take advantage of the real power of a mysql server.

Ok, let’s get going.

This is the database diagram for a default wordpress install

WP3.0-ERD

After a detailed inspection you will find that you just can’t delete rows on the wp_post table directly, as it would leave a lot of leftovers in the database.

This is all that depends on every wordpress post

  1. Revisions. You’ll find them on the same wp_post table, related to the post on the post_parent field
  2. Attachments. The same as revisions
  3. Comments. On the wp_comments table, linked by the comment_post_ID column
  4. Comment meta. Linked to the comments by the comment_id column
  5. Terms. Category terms associated with each post. They lay on wp_term_relationships table column object_id
  6. Post meta. Linked to the post in the table wp_postmeta column post_id
  7. Taxonomy count. Table wp_term_taxonomy. This is not related directly to the post, however, it contains the count of how many posts exist on each term, so the count must be recalculated after any change.

So, in order to delete a single post we have to take care of all those elements

Also (and very important) we have to do that with the elements that give us maximum performance such as temporary tables, indexes and joins.

Script to bulk delete old posts without leftovers

Note: You can execute one query at a time, to avoid timeouts

WARNING: BACKUP YOUR DATABASE, DON’T GO ANY FURTHER BEFORE MAKING A FULL BACKUP

– create temporary table of IDs to delete
DROP TABLE IF EXISTS poststodelete;
CREATE TABLE poststodelete (ID bigint(20) unsigned NOT NULL,PRIMARY KEY(ID));

– insert ids to delete (THIS IS THE QUERY THAT YOU CUSTOMIZE)
insert into poststodelete
(SELECT ID FROM wp_posts WHERE post_type = ‘post’ and post_date<’2013-04-01′);

– include revisions and attachments for deletion
insert into poststodelete
(select a.id from wp_posts a inner join poststodelete b on a.post_parent=b.id);

– Delete comment meta and comments
DELETE a, b
FROM wp_commentmeta a
inner join wp_comments b on a.comment_id=b.comment_id
inner join poststodelete c on b.comment_post_ID=c.id;

– delete terms asociated with post
DELETE a FROM wp_term_relationships a inner join poststodelete b on a.object_id=b.id;

– delete postmeta
DELETE a FROM wp_postmeta a inner join poststodelete b on a.post_id=b.id;

– delete the post
DELETE a FROM wp_posts a inner join poststodelete b on a.id=b.id;

– update taxonomy count
update wp_term_taxonomy t set count=(select count(*) from wp_term_relationships where term_taxonomy_id=t.term_taxonomy_id);

– delete temporary table
DROP TABLE IF EXISTS poststodelete;

This is the result of my database

Before

before

After

after

Other scenarios

The prior query is the fastest I could accomplish with my knowledge of mysql, however you might still experience timeouts, as the delete operation in mysql is heavy.

The solution is changing the second query to delete the first 10,000 rows and repeat:

– insert ids to delete
insert into poststodelete
(SELECT ID FROM wp_posts WHERE post_type = ‘post’ and post_date<’2013-04-01′ limit 10000);

¿Are you a mysql expert?
¿Can you help me improve the query?
¿Can you show me your own benchmarks?
Please comment

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s