Home » Technology » How to clean wp_commentmeta and wp_postmeta tables?

How to clean wp_commentmeta and wp_postmeta tables?

written by Anand March 26, 2014

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”.

Clean wp_commentmeta Table

WordPress wp_commentmeta Table Backup

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.

SQL Query for wp_commentmeta cleanup

SQL Query for wp_commentmeta clean up

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:

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.

WordPress wp_commentmeta and wp_postmeta Tables After Clean Up

WordPress wp_commentmeta and wp_postmeta Tables After Clean Up

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.

Related Articles

15 comments

peter October 15, 2014 - 11:17 am

very very useful! thanks!

Reply
Chris November 29, 2014 - 10:54 am

Hi,

Appreciate your guidance.

One of the tables that have bloated over time is the “relationships” table and would like to know is there a plugin or sql query to clean this particular table.

Thanks in advance.

Reply
Veronica Simon March 10, 2015 - 4:03 am

Hi, that was very helpful, i was looking for these lines “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;” . Thanks again 🙂

Reply
dannyV. June 15, 2015 - 10:30 am

So if my table isn’t following the same wp_postmeta – should I change it to read — abc_postmeta? what about the wp.ID does that need to change as well? abc.ID ?

Reply
Anand June 15, 2015 - 11:05 am

Yes for abc_postmeta. No wp.ID does not have to be changed.

Reply
dannyV. June 15, 2015 - 6:10 pm

Thank you for the reply. This has been a great read.

Reply
Paul Voorberg November 28, 2015 - 5:34 am

That was very useful Anand! Thanks. I reduced my post meta table from 35 MB to 5 MB using your query. Bookmarked your page for future reference.

P.S. Is there also a similar query that I can use for the wp_options table?

Reply
Anand November 29, 2015 - 9:19 am

Not that I know of. It could help me too.

Reply
Krishan Kumar December 20, 2015 - 2:14 pm

This is awesome stuff Anand, U must create a tutorial video showing everything written in this post, that would be more helpful and easy to learn and understand.

Reply
mathi April 1, 2016 - 12:03 am

Thank you for your excellent post. I did same for my postmeta table but this table still contain woocommerce data, old themes and plugins data. Please tell me how can I remove woocommerce and themes data too from this table.

Regards

Reply
Mike April 27, 2016 - 2:37 pm

On both of these queries, I get this: # MySQL returned an empty result set (i.e. zero rows).
Does that mean it didn’t do anything?

Reply
Alejandro April 28, 2016 - 3:17 pm

If you’re running the queries and no results are displayed, it just means the tables are clean. You don’t need cleaning them since they do not have entries.

Reply
contactlenssg August 28, 2016 - 10:28 pm

Thank you for your post! However I am having issues.

There an error message SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL; LIMIT 0, 100

Please advice 🙁

Reply
AppMarsh December 5, 2016 - 9:18 am

hi, what does this command line do “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;” ?? Thanks

Reply
Enric Tomàs February 10, 2017 - 8:45 pm

AWESOME! Big thz from Barcelona…350Mb less from Spam and whatever and now is only 7,2Mb
Priceless.

Reply

Leave a Comment