Introduction #
WordPress is the world’s most famous CMS web application and powers over 40% of the websites online. Because of its popularity, countless vendors, communities, and software companies provide themes, plugins, and integrations for WordPress. This fact makes it the top choice for anyone looking to start a website and achieve whichever goals he has. Being famous has many perks but also has its downsides. Due to its extensive accessibility, there are countless tools out there that you can use with it. Unfortunately, many of them are poorly coded and result in database inflation which commonly leads to slowness, lousy performance, and database table crashes leading to your website being down. As a webmaster, this is the last thing you want to experience. This article will provide great insight into keeping your WordPress database neat, your website quick and snappy, ultimately saving yourself an unnecessary headache. Let’s get started!
How to check your WordPress database’s size #
Before you begin optimizing your database, you need to know how big it is. To do so, you will most likely have access to cPanel, or phpMyAdmin, where you have the means.
Common reasons for growing WordPress database #
We have made a fantastic tutorial on how to reduce the size of your database that we highly recommend reading! There we have explained the most common reasons for database growth and how you can counter them. In this tutorial, we will focus on WordPress, and we will give you some tips on how to manage the size of your database correctly.
Sadly, as famous as WordPress is, it does not offer a built-in tool for database management. Fortunately, the vast community has developed tools that you can use in the form of plugins to make your task a lot easier.
Let’s go over the most common issues you are likely to face with WordPress.
Unused plugins which you disabled #
There are perhaps hundreds of thousands of WordPress plugins out there. Unfortunately, not all plugins are created equal, and the ease of creating them, combined with the simple installation, makes this combination a recipe for disaster. Many WordPress users suffer from excessive database size, and in a lot of cases, they used plugins in the past that they disabled. Disabling these plugins will leave all the database tables associated with them inside your database, which will unnecessarily bloat it. Please take a close look at your WordPress plugins and remove the ones you don’t use to reduce the database size. You will be genuinely surprised about the positive effect this will have on your database.
Note that some poorly coded plugins will still leave their database tables even after you delete them. In this case, please use phpMyAdmin and DROP, a database table added by plugins that you no longer use. Typically, this database table name will include the plugin’s name.
Spam and Trash comments #
First and foremost, WordPress is a blogging application, and it has a built-in commenting system. Many people do use it for this particular purpose, and sometimes, they neglect the massive number of spam or trash comments their website accumulates. The problem is that WordPress keeps these comments inside its database. Please delete all the spam and trash comments from your WordPress admin area to reduce the database size. To do so, please log inside your WordPress backend. Afterward, click on the “Comments” option on the left side of the screen.
Click on the “Spam” or “Trash” tab, mark all the comments and from the DropDown menu, select “Delete Permanently”.
Revisions #
While writing a post, every 2 minutes, WordPress will create a “draft” of that message and store it in your database. It does this to ensure that if your internet crashes, your electricity dies, or something catastrophic happens, you will still have e recent version of your post. Unfortunately, WordPress stores this “revision” inside the “wp_posts” table. To get rid of these revisions and reduce your WordPress database’s size, you can use phpMyAdmin. The easiest way is to execute an SQL query. The query you should use is as follows:
DELETE FROM wp_posts WHERE post_type = "revision";
Please be sure to replace the “wp_” prefix with one of your databases if it uses a different one, otherwise, the query will not work.
Finally, to ensure that this does not happen again, you can altogether disable revisions by adding the following code to your WordPress configuration file:
define('WP_POST_REVISIONS', false );
Akismet plugin database bloat #
Akismet is notorious for filling up your database with unnecessary comment meta. Keeping an eye on the “wp_commentmeta” table is a wise decision as it can go out of control if left unchecked. Here is a handy database query you can execute to truncate the unneeded information stored by Akismet:
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
DELETE FROM wp_commentmeta WHERE meta_key LIKE ‘%akismet%';
We have a great tutorial on how to run a MySQL query here. Of course, please replace the “wp_” database prefix with the one used by your WordPress. Here are two additional database queries, which you can safely run to reduce your WordPress database size:
DELETE FROM wp_postmeta WHERE meta_key = "_edit_lock";
DELETE FROM wp_postmeta WHERE meta_key = "_edit_last";
The first one will remove the “_edit_lock” entries that WordPress stores each time you edit a post or page. The second query will remove the “_edit_last” entry saving the user’s ID who last made a change to a post/page.
Exessive number of Transients #
To make each plugin and theme work faster, WordPress developers use a transient API. WordPress stores this temporary data inside its database, and while it can make the plugins and themes quicker, it may bloat up your database. You can compare Transients to cookies – the difference is that cookies are stored in the user’s browsers, while transients are stored in the database, making them more secure. Sometimes, if left unchecked, these transients can go out of control and add 200000 or even 500000 entries inside your database, making your site slower and your database bigger! Once again, you can resolve this issue with a database query:
DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%');
Please replace the “wp_” database prefix with the one used by your WordPress.
Please note that all the reasons for a bloated WordPress database we covered are the most common ones, however, in some cases, there could be another issue. Suppose none of the above methods help you fix your database’s exessive size, and we highly recommend consulting with a certified WordPress developer. He should be able to review the site’s database and reduce it.
Additional methods to reduce your WordPress database size #
Perhaps the most straightforward way to reduce your WordPress database is to use the “Optimize” functionality of phpMyAdmin. An optimization from phpMyAdmin can severely reduce your database’s size, removing overhead and “orphan” database tables that the site is not utilizing. It is also a very safe method as it will not bring any harm to your site.
Lastly, the thing which you can use to optimize your WordPress site’s database is the thing that can also make it grow out of control – Plugins!
Don’t be surprised. WordPress has a plugin for literally anything, and database optimization is, of course, one of them. There are two great plugins you can use to achieve this: WP-Optimize and Advanced Database Cleaner.
A few caching plugins offer database optimization functionalities, such as LiteSpeed Cache, WP-Rocket, and Swift Performance. We will not cover the steps to optimize and clean your WordPress database with these caching plugins, as we have produced detailed tutorials on all of them:
If you are not sure how to install a WordPress plugin, be sure to contact our support team
*Note: Before performing any of the plugin optimizations, we HIGHLY recommend taking a backup of your website’s database.
WP-Optimize #
Once you have installed WP-Optimize, please click the newly added option to your WordPress menu “WP-Optimize”. Click on it to go to the tool you need – namely the “Database” optimization.
Please use the checkbox listed in the screenshot to select all the optimization features and then click on the “Run all selected optimizations” button above it. That’s it! You’ve done it.
Advanced Database Cleaner #
Once you install the Advanced Database Cleaner plugin, please click the newly added “Advanced DB Cleaner” option on the left-side menu. Similar to the WP-Optimize plugin, use the checkbox to select all optimization features, use the dropdown menu to select “Clean”, and finally hit the “Apply” button. There you have it – after a few minutes, the plugin should optimize your database.
Conclusion #
With this, we conclude our tutorial on how to optimize your WordPress database and reduce its size. Maintenance is of the utmost importance when it comes to databases, as it can severely hinder your WordPress site speed and performance.
