I was working on the site of one of my regular maintenance clients, his site was running very slowly, both on the front end and the back end dashboard.
I was also unable to run a backup correctly so I began to dig a little deeper to keep the site on-line. This is when I found the site was not cleaning up comment spam correctly.
Dodgy Database
It’s long been my experience that if the dashboard of a WordPress site is running slowly, there is probably an issue with the database so that’s where I began my investigation.
I opened up the database and found something very unusual …
Comments Meta Table
The comments meta table was absolutely massive.
The comments meta table contains the actual text content of the comment. There is a corresponding part of the comments system which contains email, date left etc, but comment meta contains the actual message.
The total size of the database was approx 250MB and the comments meta table took up over 220MB of that space.
When there are lots of spam comments with heavy amounts of comment content, it ends up the in the comments meta table creating a big performance hit on your site.
Deleting Spam Comments
Now the difficult part of the problem was this, there were very few comments in the system, all the spam comments had been caught and deleted BUT the comments meta table was not tidied and optimized.
There were tens of thousands of orphaned meta entries taking up all that space.
A great plugin to use to see how large your database tables are is WP-DBManager https://wordpress.org/plugins/wp-dbmanager/.
This plugin allows you to see a list of tables and how large they are. Here is a screen dump of my comments tables, you can see I have 13MB in my comments meta table (security note always hide your table prefix).
How I Fixed This
I installed and configured a secret weapon in my tool kit, the wp-optimize plugin https://wordpress.org/plugins/wp-optimize/.
wp-optimize is a cool little plugin, it does a great job of cleaning up orphaned data which is left in the database, I’ve written more about it here when I talk about optimising your database. Check out that post on details on how to use wp-optimize.
Super Speedy Site
Once the orphaned meta data was removed, the site kicked back into normal speed, I could take my maintenance backups, “Jobs a good un” as they say in the north of England where I live.
Wrap Up
If you are suffering from a slow loading website in the dashboard as well as on the front end of a site, it may be an issue with your database, check out these optimisation procedures.
If you want a team of pros to manage little issues like this then why not take out a maintenance plan. There is a lot more than just updating plugins in this service package.
Photo Credit: Grumbler %-| via Compfight cc