Prevent Hidden Causes by Clean up WordPress Database Bloat Issues

After running your WordPress website more than 2 years, you will find your database size bigger than your thoughts. Over time WordPress saves a ton of information to its database, which can become bloated with unnecessary values, thus slowing down your site and putting more stress on your server setup. WordPress database bloat may be the hidden cause of a number of issues your blog may be experiencing!
Just like anything, your WordPress blog also needs some maintenance, servicing and cleaning every now and then to keep it running smoothly, speedily and effectively. In this post we’ll look at 10 ways you can give your WordPress install a spring clean to trim the fat and put everything in its place. So then, it helps you prevent hidden causes may occurring to your WordPress website.

Backup your files and databases

The first thing to do before you start cleaning job is backup WordPress files and databases to prevent unwanted incidents occurred. I’ve wrote an article about Backup WordPress files and databases, read it if you aren’t.

Cause of Database Bloat

• Spam, Trashed comments and Comments-meta: 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.
• WordPress Post Revisions: WordPress automatically saves multiple copies of your posts while they’re being written so they can be restored if an editing mistake is made. This can be quite handy if you’re running a multi-author blog, but for most of us it means there’s an insane number of duplicate posts bloating our databases.
• WordPress Post Meta information: Every post in your database is saved with a collection of information. Such information can bloat your database with values that are no longer relevant.
• Unused settings from old plugins: if your site has been live for a number of years, think of all the plugins you’ve installed and since removed in that period of time. Well built plugins will clean up after themselves, but others might have left their settings as values in the options table of your database.
• Un-Optimize WordPress database tables: Over time, data in the database due to constant update and deletion of records gets fragmented, creating overhead. Optimize MySQL tables can be removed and it can speed up database queries.
• Large Log Files: There are too many plugins store logs in your database then it increase the size very fast. I’m using Redirection plugin to fix broken link, after 2 weeks since it installed, I found 8000+ log pages and my database increase double size.
• Database Bloat by Transients: Transient records are temporary data stored by WordPress and plugins. They have expiration data, and if they are missing, they will be regenerated on first use.

Clean up WordPress Database Bloat Issues

Smart Cleanup Tools

This plugin is an easy to use, powerful plugin for database cleanup with total of 39 cleanup, reset and removal tools. With this plugin you can remove data that is no longer in use, or it is unneeded. It allows you to remove the redundant data within few clicks. Smart Cleanup Tools is a effective tool to keep your database clean and surely save a lot of space of your MySQL Database.

WP-Optimize

WP-Optimize reports which database tables have overhead and wasted spaces also it allows you to shrink and get rid of those wasted spaces. It allows you to remove post revisions, comments in the spam queue, un-approved comments within few clicks.
Features:

• Remove the WordPress post revisions
• Clean the WordPress auto draft posts
• Delete all the comments in the spam queue
• Remove all the un-approved comments
• Apply MySQL optimize commands on your database tables without phpMyAdmin.
• Display Database table statistics. Shows how much space can be optimized and how much space has been cleared.
• Visible only to the administrators.

Artiss Transient Cleaner

“Transients are a simple and standardized way of storing cached data in the WordPress database temporarily by giving it a custom name and a timeframe after which it will expire and be deleted.”

Unfortunately, expired entries will only be deleted if you attempt to access the transient again. If you don’t access the transient then, even though it’s expired, WordPress will not remove it. This is a known “issue” and is due to be corrected at some point in the WordPress core code.
Why is this a problem? Transients are often used by plugins to “cache” data (my own plugins included). Because of the housekeeping problems this means that expired data can be left and build up, resulting in a bloated database table.

Meantime, this plugin is the solution, using the same proposed method as the WordPress core change will use. Simply activate the plugin, sit back and enjoy a much cleaner, smaller options table. It also adds the additional recommendation that after a database upgrade all transients will be cleared down.

WPDBSpringClean

Scans your WordPress system and identifies and deletes unused database tables from uninstalled plugins. This plugin also allows you to optimize DB Tab. By default if no search criteria is specified, the plugin will identify all tables which have an overhead of greater than 10%. (Note: “Overhead” in this plugin is defined as (Data_free/Data_length) expressed as a percentage and where Data_free and Data_length are MySQL table parameters)

Selfish Fresh Start

This WordPress plugin Removes some, in my opinion, unused crappy dashboard, post & page widgets, checks for and nukes Hello Dolly, removes junk header tags including the generator tag for extra security, removes update notifications for non-admins, removes old user profile fields like aim, prevents self pinging, removes smilies and track backs, and a few other settings that nobody needs either. This is built to be very generalized so it’ll work with every site as a good clean-up fresh start and help keep clients out of the edit menus.
In next page, you will know how to Clean up WordPress Database Bloat Issues by yourself without plugins

Clean up WordPress Database Bloat Issues by yourself

Remove Database Bloat by Transients

Transients are merely cached data, so according to what I find to be true, all transient data can be safely deleted from the options table.*

DELETE FROM `wp_options` WHERE `option_name` LIKE "%_transient_%"

Disable Dashboard crap content

After logged into WordPress Dashboard, you will see a ton of information like: WordPress Blog Feed, Incoming Link… You can remove and disable it by place the function below to functions.php

/*
Diable WordPress Dashboard Crap Content
*/
function narga_disable_dashboard_crap()
{
  global $wp_meta_boxes;
  unset(
    $wp_meta_boxes['dashboard']['normal']['core']['dashboard_incoming_links'],
    $wp_meta_boxes['dashboard']['normal']['core']['dashboard_plugins'],
    $wp_meta_boxes['dashboard']['side']['core']['dashboard_quick_press'],
    $wp_meta_boxes['dashboard']['side']['core']['dashboard_recent_drafts'],
    $wp_meta_boxes['dashboard']['side']['core']['dashboard_primary'],
    $wp_meta_boxes['dashboard']['side']['core']['dashboard_secondary']
 );
}

add_action('wp_dashboard_setup', 'narga_disable_dashboard_crap');

Remove Comments-meta

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

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

Delete And Disable Post Revision

When writing a blog post, WordPress automatically save copies of your writing so that a previous post can be restored in case of errors. Run this SQL query to clear all the stored revision:

DELETE FROM wp_posts WHERE post_type = 'revision' ;

To stop/turn off post revision, add the below line to wp-config.php:

define('WP_POST_REVISIONS', false);

Delete Unused WordPress Themes & Plugins

Outdated WordPress Themes & Plugins contains many hidden risks, delete it if you don’t plan using it in near future.

Conclusion

A cluttered database can cost you SEO ranking and viewership by causing your site to load too slowly. With the above few tips, you’ll be able to optimize, clean and make your database light-weight. This isn’t uncommon, or hard to resolve, but it’s not a good state for the blog to be in as it sets the blog up for a wide number of issues including failed backups. If you have any database optimization technique to share, feel free to do so, I’ll be happy to hear from you.