I had some kind of issue with my spam filter, and my database was filled with like 6000 pages of spam (30 per page, so round 180 000 spam comments). And the thing is, that they take up a LOT of data space. I started to sort them out using the Drupal UI, but later found that the database still was rather large (~150 MB compressed). I took a look in the database and found that the data values where still there, altough I’ve removed the linkage of the comment from the nodes. So i still had these ~ 200 000 spam comments.
Well, I have plenty of backups, and altough 150 MB isn’t that much it becomes quite a lot in time – so I wanted to remove the data values in the database too, and here’s how you do it:
The table ”comment” contains the ”visible” comments. It also contains a comment id (cid). The two tables ”field_data_comment_body” and ”field_revision_comment_body” contains the full comments. These are the rows we want to remove. the table ”comment” shows only the comment which I’ve already ”deleted”, but the other two still has the comments and use a lot of space. So by a simple MySQL Query, they will be gone, by using the cid’s from ”comment”.
I use phpMyAdmin, and chose the database of this site, and clicked the mysql button, then pasted this and ran it:
Delete from field_data_comment_body:
DELETE FROM field_data_comment_body
WHERE entity_id NOT IN
(SELECT cid
FROM comment
WHERE cid is NOT NULL
)
And then from field_revision_comment_body:
DELETE FROM field_revision_comment_body
WHERE entity_id NOT IN
(SELECT cid
FROM comment
WHERE cid is NOT NULL
)
What is does? Well, it removes all the rows from the field_*_comment_body tables that does not have a entity_id that corresponds with the cid from the comment table.
As easy as that! Now you only have the comments which you agreed to using the Drupal UI. And my compressed database backup as shrunk to 15MB(!).