Just about me, and stuff...

Dealing with wp_commentmeta when moving WordPress databases

I've spent a few days moving WordPress sites around between servers recently. A relatively simple if not mundane task, copy all the files from one server to another using SCP in terminal then moving the database, link the two up and all is well. You can move SQL by using command line in terminal, but […]

I've spent a few days moving WordPress sites around between servers recently. A relatively simple if not mundane task, copy all the files from one server to another using SCP in terminal then moving the database, link the two up and all is well. You can move SQL by using command line in terminal, but I prefer to download it, then create a new database & user and upload it to the new server.

While I was moving stuff around I noticed that the wp_commentmeta table on some of the sites was particularly big and cumbersome, like 50mb cumbersome

wp_commentmeta

So I did a bit of poking around and found that even after comments had been sent to spam and deleted there was still a chunk of information about them sitting in the wp_commentmeta table. After a bit of asking Google and my basic SQL knowledge, I did a comparison of what was in wp_commentmeta and not in wp_comments.

To find this out I ran the following query in PHPMyAdmin

Select * FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)

I found on one site that had been subject to quite a lot of spam that there were 70,000+ entries in the meta table that didn't have a comment in wp_comments, this accounted for several mb of data that I really didn't need to be moving so I took a chance on a site that didn't matter if I broke it, at deleting anything that was in wp_commentmeta but not in wp_comments by running the following command

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)

it seemed to do the trick, reduced the size of the database significantly and everything continued to work after my mass deletion.

So if you find that you have a particularly oversized wp_commentmeta table, maybe this will help you out.

Remember, always back up your database before performing any kind of open heart surgery on the tables.