Cleaning up your tables from time to time is a job every webmaster should do to keep the site running smoothly. It is generally a good idea to take a look at each table that is somewhat bloated (e.g. larger than 100MB)
Comment Meta Table
One particular table that gets bloated very quickly if you have a busy site with lots of spam, is the comment meta table. It can be twice the size of the actual comment table because Akismet keeps adding useless meta data for all comments.
To get rid off this junk run:
DELETE FROM tableprefix_commentmeta WHERE meta_key LIKE "%akismet%"
If you first want to take a look at it:
SELECT * FROM tableprefix_commentmeta WHERE meta_key LIKE "%akismet%"
Get Rid Of The Overhead!
Next, after running this query you will surely notice a large number in the column “overhead” (it’s always the last column):
Scroll down and at the bottom select “Check all” and then from the dropdown field select Optimize table
Also read our guide Delete WordPress Comments Labeled Spam