How to clean wp_commentmeta and wp_postmeta tables?

If you use WordPress and have been blogging for a while, the chances are your wp_commentmeta and wp_postmeta tables are huge and significantly larger than the actual wp_comments and wp_posts tables. Yes, it does not make sense that the meta data associated with comments and posts (the actual content) is a lot more than the content itself. In this post, let me show how to clean wp_commentmeta and wp_postmeta tables significantly reduce the size of your WordPress database, something I learned recently. [Read: 5 blogging mistakes I made when I started this blog].

Problem with wp_commentmeta and wp_postmeta

Take a look at this example. In one of the blogs, which was regularly optimized with WP-Optimize but never manually cleaned, the following are the sizes of comments and posts tables:

• wp_comments - 800 rows and 390.8 KiB
• wp_commentmeta - 5,387 rows and 6.0 MiB
• wp_posts - 1,300 rows and 3.7 MiB
• wp_postmeta - 11,947 rows and 3.5 Mib

Why are the comments and posts meta data tables bigger than the comments and posts themselves? That's because both wp_commentmeta and wp_postmeta tables can be quickly filled with junk or obsolete data such as Akismet validations and meta data of deleted posts and revisions. So let us see how to quickly clean wp_commentmeta and wp_postmeta and optimize them to clean up database.

Precautionary Steps - Backup Tables

Realize that we are directly messing with the WordPress database and mistakes could make your site inaccessible. So it is always best to backup your database. I am going to show how easy it is do that from within phpMyAdmin. [Read: Automatic MySQL database backup on GoDaddy shared hosting].

Login to your phpMyAdmin interface and click on you wp_comments meta table. Then, as shown in the picture below, under Operations->Copy table to, enter a name for the table backup. In this case I chose wp_commentmeta_bk. Then click "Go".

You should now have a new table with the name wp_commentmeta_bk. Repeat the same setups for wp_postmeta table. You may enter "wp_postmeta_bk" as the name for backup or choose your own name. Alternatively, you could export the whole database as a file and save it on your computer.

Clean wp_commentmeta and wp_postmeta tables

Cleaning wp_commentmeta

First let us clean wp_commentmeta table. As shown in the picture below, go click on your database name on the left side and then go to the SQL tab.

In the text box and as shown above, enter the SQL query give below. Ensure that a semi-colon is selected as the delimiter and the press "Go".

SELECT * FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments );
DELETE FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments );
SELECT * FROM wp_commentmeta WHERE meta_key LIKE '%akismet%'; 
DELETE FROM wp_commentmeta WHERE meta_key LIKE '%akismet%';

There are 4 separate SQL queries that are executed in sequence. First, we select the commentmeta data for comments that do not exist (spams or deleted comments) and then we delete them. Next we select commentmeta data that are Akismet validations and then we delete them. On successful execution, you should see "Your SQL query has been executed successfully". You should also see how many rows were affected.

Recommended Guides on WordPress:

• WordPress Domination - Beginner to NINJA
• WordPress: The Missing Manual
• WordPress All-in-One For Dummies
• Make Money From Blogging: How To Start A Blog & Profit From It

Cleaning wp_postmeta table

To clean wp_postmeta table, again click on the database name on the left side and then go the SQL tab. Enter the following SQL query into the textbox, ensure that semi-colon appears as the delimiter and hit "Go".

SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

Here too, we first select the postmeta data for posts that do not exist anymore and then we delete them. After execution, you should see the successful execution message and the number of rows that were affected.

Optimize wp_commentmeta and wp_postmeta Tables

We have cleaned up wp_commentmeta and wp_post meta tables but not really. The rows that were clean will reside in the table as "overhead". You will have to optimize the table to get rid of them. Fortunately, phpMyAdmin makes it very easy to perform MySQL tasks. [Read: 10 easy phpMyAdmin tweaks to simplify MySQL administration].

To optimize wp_commentmeta table, click on the table name on the left size and then go to the "Operations" tab. Under "Table maintenance" click on "Optimize table". Repeat the steps to optimize wp_postmeta table. Alternatively, you can check all tables and from the "With selected" drop down menu select "Optimize table". Now go back to the database structure view on phpMyAdmin and compare the number of rows and size of wp_commentmeta and wp_postmeta table.

Notice that wp_commentmeta table reduced from 6.0 MiB to 55.8 KiB (5,387 rows to 592 rows). That is a big reduction / saving. The wp_postmeta also showed size reduction. After thoroughly testing and making sure that you did not break your database or lose any data, you may delete the backups of wp_commentmeta and wp_postmeta tables you made.

There are several posts on WordPress forum asking how to clean wp_commentmeta and wp_postmeta and hopefully this post helps others who are having troubles.. So there you go, optimize wp_commentmeta and wp_postmeta table and clean up database to improve your WordPress performance.