How to reduce the database size of a WordPress application?
Table of Contents
In case the largest database table is different from the below-listed tables, it is most probably part of a plugin and you should search the Internet for more information with the table name as a search term. Usually, the plugin developers and support forums contain detailed information on how to resolve problems with the size of their database tables.
Enable the Scheduled Database Maintenance option in Speed Optimizer
Install the Speed Optimizer plugin and enable the Scheduled Database Maintenance option under the Environment Optimization tab.
The feature will perform the following maintenance tasks once per week:
- Perform Database Optimization for MyISAM tables
- Delete all automatically created post and page drafts
- Delete all page and post revisions
- Delete all posts and pages in your Trash
- Delete all comments marked as Spam
- Delete all expired Transients
To check the size after the optimization is performed, go to Site Tools > Site > MySQL > PHPMyAdmin > Access, select your database from the menu on the left and click on the Size column in order to sort the database tables by size. At the bottom of the page, you can check the total size of the tables.
In many cases, enabling the feature will be sufficient to reduce the size of your database.
Create a backup
In case the activation of the Database Maintenance feature from the Speed Optimizer plugin is not sufficient, the next step is to create a backup of your database. To do this, go to Site Tools > Security > Backups. Choose any name for your backup under the Create New Backup > Backup Name and press the Create Backup button.
In case your hosting plan does not include the feature to create on-demand backups, you can navigate to Site Tools > Site -> MySQL > PHPMyAdmin – Access. From the tool, select your database from the menu on the left and click Export > Go. The database backup will be downloaded to your computer and can be restored if this is needed.
This step is important because in case anything goes wrong with the database optimization that you will be performing, you can restore the database from the available backup.
If you haven’t done any changes to the website in the past 24 hours and there are no new registrations and orders on your website, you can skip that step and just restore the automatic backup that we have created for you. We keep up to 30 daily backups for Shared hosting accounts and up to 7 daily backups for Cloud servers.
Check which are the largest database tables
To check the largest database tables, navigate to the PHPMyAdmin tool, select the respective database from the menu on the left and click on the Size button at the top. The database tables will be sorted by size.
Then, check the name of the largest database table. This information may be helpful to determine if the table is part of the core WordPress database tables or it is added to your website by a plugin that you are using.
The “Meta” database tables
These types of tables contain additional fields that are required by your main database tables. Usually, these fields are added by plugins. The so-called “meta” database tables are the following:
wp_postmeta
wp_commentmeta
wp_usermeta
wp_termmeta
Under certain circumstances, some information may remain in these database tables although it is no longer required. This can lead to the growth of the database and it is safe to delete such information as it is not used by any of the posts, comments, users or terms.
There are some other database tables that may contain information that your website doesn’t really need. Such tables are:
wp_term_relationships
Similar to the “meta” tables, the wp_term_relationships table can also contain information that is no longer needed. It is recommended to delete this information.
wp_comments
There are two common issues with the wp_comments database table:
- Trackbacks and Pingbacks – Trackbacks allow you to notify other website owners that you have linked to their article on your website. Pingbacks were designed to solve some of the problems that people saw with trackbacks. In some cases, these features are used for spam purposes by certain bots, and if you haven’t set any trackbacks, feel free to delete them.
- Pending comments – on some WordPress websites, the administrator has to approve the comments before they are published to the website. It often happens that malicious bots submit lots of spam comments. Although they are not published, they are stored in the database and take disk space.
The easiest way to clear these database tables is via a third-party plugin. There are plenty of free plugins that can get the job done like Advanced Database Cleaner or WP-Optimize.
The issue is with another database table
In case the largest table is not part of the WordPress core tables, it is most likely associated with some of the website’s plugins.
To verify which plugin is associated with a particular table, check the Internet for relevant information by searching for the respective database table name.
When you find the plugin that creates and manages the table, you can contact the plugin’s support for further information and assistance.
What should I do if there are problems with my website after a database optimization?
Always check your website for potential problems after applying changes to its database or after installing/removing various plugins or modules. If you notice anything not working properly, you could restore the website to the state prior to the performed actions from the backup that you already have created.