Release Physical Disk Space After Deleting MySql Table

(update on March 04, 2016) Our WordPress blog sites got countless junk comments often. The big amount of junk comments were saved by WordPress into database automatically and caused MySql data tables large sized.

We could delete junk comments manually, or we install some plugins to help us, for example the plugin Akismet can protect our site from spam. It is a default and recommended plugin in newer edition of WordPress. However, no matter which way, if you set you will review all comments manually, the junk comments will still occupy your database space, your data tables are still huge. So, what you can do is deleting useless comments manually one by one or use another “rude” way which is deleting all records from comments data table.

What should you do next after deleting junk data ?

We are not going to talk about how to delete the table here (also we should know how to handle related table commentmeta ). What we care about here is something we should do after deleting records from table.

We have a screen shot below, we can see the comments table overhead size 239.9 MB, but actually records account was only 9 !  This was just because we deleted records from comments table. even we deleted big amount of data, but we can see the physical disk space was not released yet, so MySql still kept the physical space for comments table:

image

How to Release Physical Dsk Space After Deleting records from MySQL Table ?

What we should do is OPTIMIZE TABLE: we can follow the guide which is provided by MySql official website to optimize the table hw_comments of WordPress (we used phpMyAdmin on DreamHost hosting provider.)

The command is : Optimize table hw_comments

image

A litter while later we see the result:

image 

Now we go back to check the size of comments table, it is now only 8.7 kb. it is real physical space size.

image

So now you could check your hosting account that your website database usage reduced already.