Bulk delete old posts to make your aggregator WordPress website more efficient
We have many sites that are automated, posting hundreds of articles at given intervals. If this is good in order to get content, tags (keywords) and images, it obviously makes the site huge and sluggish over time.
We have posted several times our tips re:maintenance (like this one), which detail a rather thorough clean up. In this post we’d like to focus on just how to delete old posts. Very often we’re asked to do our maintenance service (available here), and we find that the site has 100s of 1000s of posts. As a consequence the sql database is enormous, causing a slow down. All these posts are not necessary for the health of the site.
There are dozens of SQL commands used to clean up the database (see link above) including removing tags, comments, transients and so on. In the following pictures we concentrate on deleting old posts.
For example if you log in into your WordPress site you may see something like this (we’ve seen much worse, often six digits in there!):
Almost 30,000 posts is a bit too much. Let’s check the sql database. Login into your Cpanel and click phpMyAdmin:
Click on your database:
A list of tables will open. Notice at the bottom of the list that the database is 184Mb! Every time WordPress makes a new post it has to go through all this data.
If you look at the tabs at the top you will see one called SQL, click on it:
In this case we want to delete all posts older than 60 days, so enter the following command:
DELETE FROM wp_posts WHERE `post_type` = ‘post’ AND DATEDIFF(NOW(), `post_date`) > 60;
and click GO. (Notice ’60’ in the string which is obviously the number of days).
Wait a few seconds to see the results:
The command has deleted all posts older than 60 days. Let’s clean up the associated (and now useless) meta-crap.We’ve seen tutorials about bulk deleting old posts which do not suggest this crucial further command!
Again click on SQL at the top and enter:
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL
Again a lot of space has been made:
If you visualize your database structure now you will see that nearly 60Mb (in this case) have been cleaned. In order to remove these for good, simply click on ‘Check All’, select ‘Optimize table’ from the dropdown and click Go.
Notice the database size now which is 115Mb (instead of 184 as it was).
If you log in into the site you will notice that the posts count has also been greatly reduced:
In the link at the top of this post we provide a link to another article showing a more thorough cleanup, but all those commands may scare some users. The procedure above offers a very quick way to at least clean up the bulk of old data.
Please notice that if you delete old posts, there is no reason to keep old folders full of pictures that are not attached (used) anymore, and only take space on the server. Again this is very simple to deal with. In your Cpanel, click on File Manager, navigate to public_html/wp-content/uploads/2016 (or whatever date, even older, this is an example), notice the folders as per picture below, select them, right-click and Delete.
Before doing this, it is good practice to visit the site, click View Source in your browser, search for 2016/02, 2016/03 etc. and make sure that there are NO links. This makes sure that you are not deleting images that are being shown.
With certain Cpanel themes it may offer to Skip the Trash, make sure to check that before deleting.
All the steps above may seem a lot of work but in fact running TWO sql commands obviously only takes a few minutes, and generally this maintenance is required only every few months.