How to Clean Orphan Records from WordPress Comments Meta Data Table
While doing routine wordpress database backup i surprised to see size of wp_commentmeta table which was 55MB. On the other side main comments table size was only few KBs. I checked both table rows and found that comments meta data table contain thousands of rows for comments that actually not exists in wp_comments table. Those comments either deleted manually or marked as spam and then deleted after 15 days but meta data records not deleted in this process. This was possibly a bug in older akismat plugin that seems have been fixed in recent version but still we need to delete orphan records to reduce database size.
- Login to your hosting control panel and access PhpMyAdmin web interface. If your are using cpanel it is pretty easy.
- Backpup your database first before doing anything.
- Open SQL Query windows and run below command. It will show all orphan comments meta data. Replace table name prefix (wp_) in query with your actual table name prefix you used during installation. You can also check table prefix in phpmyadmin.
Select * FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)
- Verify records shown in above query results. If you are sure run below command in SQL Query window. Replace table prefix with actual table prefix.
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)
- All Done.