Solving wordpress website database bloat

My website has been a little unloved this year… It’s the downside of becoming increasingly very busy with paid work and spending much of the summer away – a huge backlog of images to process awaits this winter! But it’s been an exciting year, with a third book published, and additional video skills added to my professional skillset.

In the meantime I’ve recently discovered an issue that may affect many of you with wordpress databases, and this is just a quick post that may help others in the same situation.

My wordpress databse had become very bloated. Result – the website slows down and eventually the ISP host gets upset that it’s taking up more space than it should on their servers.

So why had it become bloated? Two simple reasons – spam comments and stats.

Spam comments. Like many other folks, I use Akismet to automate the filtering of comments into those that are likely to be genuine and the 99% that sadly are no more than spam links to dodgy sites. Unfortunately, rather than delete all these, Akismet simply holds them in a queue and you have to delete them yourself. Which is timeconsuming – although I have now seen there is a “delete all spam” option which I’m sure wasn’t there in previous versions of WP/Akismet!).

So how to solve this?  (Though please, don’t attempt this if you don’t have at least a sleeve’s worth of tekki anorakness in you!)

First things first, I am assuming that you’re using Akismet and have phpmyadmin as the SQL db management tool in your ISP’s control panel. I haven’t a clue about other alternatives as I’ve not used them!

Check that you’ve approved all comments that you think are genuine through the Comments section of your WP admin panel. Backup your posts using WP Export in the Tools section. Then go to your ISP’s control panel and backup the database tables. (NB If you don’t know how to do this – and also how to reimport them should anything go wrong! – don’t proceed any further with this!)

In phpmyadmin go find the table wp_comments. With more SQL expertise, I’m sure a simple DELETE FROM command in the SQL tab should work, but unfortunately this didn’t work for me. If this is also an issue for you, a way around that seems to work is to use the “Search” tab (search for comment_approved like “spam”) and set number of records to be displayed to 5000. NB YMMV on this – I found beyond 5000 rows I didn’t get the “select all rows” checkbox at the bottom of the listing. Wait ages for this to load, scroll to the bottom, then click the checkbox that selects all rows, click delete and click through a “do you really want to do this” box. Repeat until spam comments are deleted.

Comments-meta also needs debloating

Now you’ve got to delete the bloat left by the spam comments in the wp_commentmeta table.

Here the WP forums came in very handy to find out how to do this but simply, go to the wp_commentmeta table and click the SQL tab, then use the following SQL command to clean up wp_commentmeta entries which are orphans (ie have no related entry in  wp_comments):
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

Optimise the wp_comments and wp_commentmeta table

You may well then need to optimise both the wp_comments and wp_commentmeta tables, as if they have had a lot of bloat, they’re likely to still have a large “overhead” (ie working space that it keeps in case it needs such a large space again in the near future).

To do this, go to the page in phpmyadmin that lists all the tables in your SQL database and click the value listed under “overhead” for each table. This should bring up an option to optimise it. Click that, and the size of your database magically shrinks!

For me, this reduced the wp_commentmeta table from over 300Mb to just kB, and my genuine comments still seem to be working fine 🙂

Stats

I strongly recommend just using google analytics for stats, simply as it holds the data itself, rather than bloating your web database with this. Using more than one stats program just leads to duplication, and many of these plugins can add serious bloat to your db with all the data they collect. Removing just one main offending plugin on this freed up another 300Mb, and I’m hoping all the data should still be in google analtyics (which I’ve also had running in parallel for a long while).

NB If you haven’t been using google analytics, you will almost certainly lose stats data from before you install this, but it’s a switch worth making if your database is suffering from excessive bloat.

UPDATE 2013-Jul-21