WARNING: WHAT WE DID IS SUPER DANGEROUS AND UNLESS YOU KNOW HOW TO RESTORE YOUR DATABASE, I WILL NOT ADVISE YOU TO DELETE RECORDS FROM THE DATABASE MANUALLY.
In order to manually purge a large number of posts from the WordPress database you have to make sure that you not only delete the posts but all of the entries and files related to those posts. Before we get into the SQL statements to delete the posts, let’s take a look at what the wp_delete_posts() basically does.
The images featured in this post share a common theme. Following the execution of all SQL statements outlined in this article, the system experienced a notable reduction in taxing demands. As a result, unrelated queries began to run more efficiently, contributing to overall system performance enhancement.
What WordPress does
Ok, so I am not sure exactly what the order is of how WordPress deletes a post. This is just what would make sense to me.
- Delete the entries in the wp_postmeta table that are associated with the post children(attachments, revisions, etc.) The records associated with a post are identified by post id, which is stored in the field post_id.
- Delete the post children in the table wp_posts. Child posts associated with a post are identified by parent post id, which is stored in the field post_parent.
- Delete the entries in the wp_postmeta table that are associated with the post. The records associated with a post are identified by the post id, which is stored in the field post_id.
- Delete the entries in the wp_term_relationship for the post. The relationships associated with a post are identified by the post id, which is stored in the field object_id.
- Delete the post from the table wp_posts.
The SQL statements
Again, I am not sure of the exact order that WordPress uses to delete the posts, but here is what I did: (MySQL Statements)
Create a temporary table with the ids of the post to delete.
This is not necessary, but I found it very helpful to have a temporary table with the ids vs. a sub-query. I found that having a table is faster than running the subquery multiple times. The where clause would depend on your needs. In the example posts older than 01/01/2021 are selected for purging.
CREATE TABLE IF NOT EXISTS wp_posts_to_purge (id INT NOT NULL PRIMARY KEY) ENGINE=INNODB;
INSERT INTO wp_posts_to_purge (id) SELECT id FROM wp_posts WHERE post_date < ‘2021-01-01’ and post_type = ‘post’;WARNING: Before you delete any post please first take a Backup, any delete operation done is irreversible. Please use it with caution!
Delete the entries in the wp_postmeta table that are associated with the post children
This SQL Statement will delete the custom fields for attachments, revisions, and other custom post types associated with the posts.
DELETE FROM wp_postmeta where post_id in (select id from wp_posts where post_parent in (select post_id from wp_posts_to_purge));Delete the post children
This SQL will delete the attachments, revisions, and other custom post types associated with the posts.
DELETE FROM wp_posts where post_parent in (select post_id from wp_posts_to_purge);Delete the entries in the wp_postmeta table that are associated with the post
This SQL will delete the custom fields associated with the posts.
DELETE FROM wp_postmeta where post_id in (select post_id from wp_posts_to_purge);Delete the entries in the wp_term_relationship for the post
This SQL will delete the categories and tags associated with the posts.
DELETE FROM wp_term_relationship where object_id in (select post_id from wp_posts_to_purge);Delete the post
This SQL will actually delete the actual posts.
DELETE FROM wp_posts where id in (select post_id from wp_posts_to_purge);Clean up temp table
This SQL will delete the custom table
DROP TABLE wp_posts_to_purge;
How to run the SQL Statements
Well, this is going to depend on your familiarity with MYSQL and WordPress. Also, whether you have access to your database or not.
If you have access to the database, you can just execute the SQL sequentially and you will be set, this was my preferred option because I had more visibility of what the queries were deleting.
If you do not have access to the database, you could create a plugin and execute the custom SQL statements using $wpdb->query. Remember always to sanitize, with prepare, your custom queries. This is way more work than it is needed unless you create so many posts daily that you are going to schedule this job to run frequently.
By default, MYSQL runs with the AUTOCOMMIT mode enabled, which pretty much means that each SQL statement is executed and committed independently. On the other hand, if you run your SQL Statements between BEGIN TRANSACTION and COMMIT, your changes will be rolled back if there is an error message.
Finally, whether you use one methodology or another, it is a good idea to optimize your tables. This is similar to the old Defrag in Windows and Mac. This will remove the unused space on the table.
If you liked this post, you may consider reading: My WordPress Back-end is Slow. Now what?
