Optimising WordPress Database
Optimising WordPress Database – How and Why
Databases are the life-blood of your website, especially if you are utilising a content management system (CMS). Websites using static HTML rely on storing data within the webpage itself. On the other hand, the variety of data stored by CMS and the data’s various use-cases are best served by databases such as SQL, XML, noSQL and so on. WordPress is no exception to this rule. All dynamic as well as static content along with varied other pieces of information is stored by WordPress in its database matrix.
What Is Meant By Database Overhead?
While databases are a powerful standard when it comes to dealing with information, like any other software, they require maintenance too. Lack of maintenance will see databases get bloated and slowing down your website. Even though WordPress does strive to reduce the proliferation of bloated databases, ‘database overhead’ is something every webmaster must keep tabs on.
Database overhead happens when stale data has accumulated in your database. Revisions to posts, pages, spam comments, trashes etc. all get stored within the WordPress database. While these allow for a better user experience in terms of ease of content management, if left unchecked, these can ultimately hinder user experience itself. A good analogy to draw is with disk defragmentation or even something like regularly changing the oil in your car engine.
Database overhead can be managed manually. A common practice entails clearing the overhead through phpMyAdmin. Having said that, going through phpMyAdmin is also not the ideal way to optimise databases. There are better WordPress plug-ins that will do all the work for you; the most popular and effective one being WP-Optimize.
Transient is a type of caching functionality that WordPress provides. Like any caching option, this allows you to retrieve data rapidly when it otherwise would have taken longer. However, WordPress transients are different from the garden variety caching tools in that these come with an expiration date. Transients don’t always get removed from the database when they expire and the consequence is bloating. Transient records can be found in the wp_options table unless you are using an object cache system like ‘memcached’.
Transients are hardly the biggest culprits of stale data and bloating. That being said, it is worth a few clicks to clear them out and keep your tables optimised. There is help at hand in form of several plugins that can help in dealing with transient records, expired or not. One such tool is Transient Cleaner. Another plugin – Delete Expired Transients – provides the functionality that Transient Cleaner does while also allowing you to schedule daily tasks for dealing with transients.
Unused Plugins and Themes
Almost every plugin will use your WordPress database to store information required for the plugin’s functioning. Inconveniently, this data stays even after you have uninstalled or deactivated the plugin. This is the default scheme so as to allow hassle-free re-activation of the plugin. Nevertheless, there are times when plugins are installed only for the purpose of a test run and times when you are certain about stopping to use the plugin altogether.
While some plugins do incorporate the ability to delete all related data within the plugin itself, it is definitely not the norm. Consequently, stale data accumulates, leading to unnecessary overheads. The same applies for themes used in WordPress. Again, phpMyAdmin can be used to rid your underlying databases of the required tables.
However, even with a fairly decent understanding of the 11 essential databases that form the core of WordPress’ database management system, it may not be straightforward. There is where a plugin like WPDBSpringClean shows its worth. It identifies the tables correlating to the plugins no longer in use and lets you delete them if required.
Introduced in WordPress 2.6, WordPress’ revision system has seen many database administrators experience an increase in database size. It is not surprising considering how a copy of every change in the blog post is stored by WordPress.
While this provides for the useful ability to revert to older versions of your blog right back to the ideation stage, there is very little utility to stored revisions once the blog has been published. Another important thing to note is that changes which you make to current revision settings will only be prospectively applied, meaning the older blog saves will not be affected. So do not hesitate to make changes to the iterations of revisions that are saved or maybe even contemplate doing away with revision save altogether.
There are a few plugins that allow you to accomplish this task. It can be done through MySQL as well but you need to be absolutely sure of the command lines being used. Optimize Database after Deleting Revisions has shown some great results. Additionally, it can also be used to manage transients and spam items. Plus, the scheduler functionality can be used to great effect.
Keeping your database clean and optimised has a very subtle but effective impact. As the scale of your website grows, so will the bloat and consequently the website delays. It is highly undesirable and can affect conversions – possibly the last thing you want as a website that is scaling up. The above optimisations will be of great use in keeping your WordPress database in order.