An optimized database greatly improves your blog’s performance in terms of page load speed. Think of the database as your blog’s engine. It is where your entire site’s content is stored. In other words without it there is no website. You always want to have a clean, well-optimized database and also have the latest copy stored safely in a reliable location.
Just as you need to recharge your mind and body through exercise and meditation, your WordPress database needs periodic optimization to perform optimally. Every fresh WordPress installation creates a database with 11 tables – see full description of the core database here on wordpress.org.
With time, this number grows as you install plugins and other add-ons. Some of the data added to the database becomes stale with time. For instance, draft copies of your old posts, pingbacks, old comments, and spam info kept in the database is all basically unwanted data. Not only does this stale data take up disk space but also slows down your website. The bigger the database, the longer it takes for data to be retrieved and displayed on the relevant pages.
Cleaning up and optimizing your database mainly entails removal of stale data to free up space and improve page loading times. If you’re using a hosting plan that limits you on space, the space you free up may prove particularly valuable.
There are several ways to clean up a WordPress database. The most popular method is using a plugin, but you can also use phpMyAdmin or the built-in optimization tool in WordPress. You can also query the database directly to identify and remove unwanted data.
Using a plugin is straightforward as you’d expect. However, any plugin that has direct access to your database may pose a security risk: if an unscrupulous person gains access to your back-end (and therefore your plugins), they could be up to anything.
The other disadvantage of plugins is that they add their own bloat (settings and other plugin-specific data) to your database. This data isn’t automatically removed when you deactivate the plugin. Some plugins provide settings for removing this data but the majority don’t.
Generally if your blog is new and still small, you’d be fine using phpMyAdmin to do the cleaning and optimization. Consider an advanced plugin if your blog is big and contains considerable data.
Using a Plugin
Despite some pitfalls of using a plugin to clean up your database, this method is still the most user friendly option provided you take care of your site’s security. Advanced database optimizers can scan your database in seconds and evaluate its current status before suggesting the best course of action – what to delete and what to keep.
On the other hand, if you were to run SQL queries directly against your database, you’d have to scan table by table to identify unwanted data and delete it. Obviously this isn’t an intuitive way of doing things so for most users, the sheer convenience of using a plugin outweighs the potential risks they bring.
To use a plugin to clean your database, simply download and install one. Obviously, every plugin has its own usage instructions. If you’re not using a plugin, deactivate it, and remove all of its data.
With this plugin you can easily delete stale info from your database to make it lightweight. Stale info targeted by WP Clean Up Optimizer includes post drafts, post revisions, old comments, orphaned info (such as post meta), and pingbacks. The plugin also has optimization and repair features.
The basic version is free but offers limited features. The Pro version comes with advanced features such as scheduled cleanups and premium support.
WP Optimize cleans up and optimizes your database with a single click. You can set it up to automatically delete extraneous info once a week. Once installed, you’ll see a new WP Optimize menu. Clicking on it takes you to a database management dashboard that’s pretty user friendly.
It’s completely free to use as there’s no Pro version. For this reason there is no premium support offered and you might have to rely on WP forums for help.
If you don’t want to go the plugin route, you can clean up and optimize your database by running SQL queries directly against the database. Using phpMyAdmin, you can see the size of each table in your database – look at the Size and Overhead columns at the extreme end of your database. Of course the more data a table stores the bigger its size.
Overhead refers to temporary storage space for queries run against tables in the database. As more and more queries are run against the tables over time, their overhead increases but it seldom affects performance. Nonetheless, optimizing your database clears overhead and makes your database slightly smaller, which is the optimal situation.
To optimize your WordPress database via phpMyAdmin, click on the Check All box just below the table listing and then select Optimize table from the dropdown menu next to the Check All box.
You will get a confirmation message once the repair process completes.
The dropdown list has one more option for cleaning up your database; the Repair table option fixes a corrupted table.
In addition to phpMyAdmin, WordPress comes with a built-in tool for optimizing your blog’s database. To use the tool, you need to first enable it by adding this line of code in the wp-config.php file:
define( 'WP_ALLOW_REPAIR', true );
Once you’ve edited and saved wp-config.php, you can access the tool at http://www.yourdomain.com/wp-admin/maint/repair.php and then you can choose between two repair options: Repair only or Repair and optimize.
On the first run, the tool may not be able to optimize all tables in the database. You only have to run it again. Notice that you don’t need to log into your dashboard to run this tool, meaning that anyone can actually run it provided it is enabled in wp-config.php, which is not ideal. So you’ll want to delete the define( ‘WP_ALLOW_REPAIR’, true ); line once you’re done running the tool.
Cleaning up your database periodically is important to keep out bloat and make the database lightweight. Using phpMyAdmin is a hassle-free way to remove unwanted data and optimize your tables fast. You can use plugins as well or run direct queries if you know what you’re doing.
How often do you clean up your database? What tools do you use?
We never spam. Subscribe to receive inspiration, news, and ideas in your inbox.
Brenda Barron is a writer from southern California. Her work has appeared on sites like Elegant Themes, WPMU DEV, and VentureBeat. She also blogs at Digital Inkwell about the life of a freelance blogger.