Release Physical Disk Space After Deleting MySql Table

2013-12-14

(This article is copied from our sister website)

Our WordPress blog sites always got countless junk comments, this caused our MySql data table large size.

We could delete junk comments if we have not installed plugin such as Akismet to protect our site from spam in WordPress Admin Dashboard, but we might have to delete the comment one by one so very slow, or, we use another "rude" way, delete records from comments table.

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 comments table overhead size 239.9 MB, but actually records only 9 !  This is just after we deleted records from comments table. We can see the physical disk space not released yet, so MySql always keep the physical space for comments table:

image

What we should do is OPTIMIZE TABLE: 

optimize table hw_comments  (we used phpMyAdmin)

image

A litter while later we see the result:

image 

Now we go back to check current comments table size, 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.