Your WordPress site can be slow for a variety of reasons, including a bloated database running in the background. To solve this problem, professional WordPress maintenance includes regular database optimization. This allows you to remove unnecessary data, as well as correct errors and inconsistencies in the database.
Optimizing your WordPress database does not require any special programming knowledge, although you may need to edit the wp-config file. In this article, we will provide you with some tips for optimizing your WordPress database.
START FROM BACKUP
You should always back up your database before starting optimization. This will help you save your data and return to the previous version of the site if something goes wrong. There are several ways to back up your WordPress site:
REMOVE CONTENT THAT IS NOT REQUIRED
One of the most important aspects when optimizing your database is removing unnecessary content on your website. Since every post, page, comment, and post edit is a database item, you can free up a lot of space by deleting unused, outdated, or duplicate content right from the WordPress admin area.
If your WordPress site has been around for a long time, you may find such content in different places. The first step is to delete the Trash folder for your posts and pages (even though WordPress automatically deletes deleted posts after 30 days). You can also check drafts and pending messages to make sure you really need them.
You can also check if you have any taxonomies that you are not using. By default, WordPress has two types of taxonomies: tags and categories; you can find them in the Messages menu. Optimizing your database also provides an opportunity to rethink your taxonomy structure, eliminate duplicate tags and categories, and improve the searchability of your posts
In addition to default posts and pages, you may also have custom post types such as calendars or affiliate links, which may include content you no longer need.
REMOVE THEMES AND PLUGINS YOU DO NOT USE
WordPress stores themes and plugins in the wp-content folder, not in the database. However, it is recommended to remove themes and plugins that are not used. Some of the more complex themes and plugins create additional tables in the database. In addition, all theme and plugin configuration options are stored in the wp-options table.
Plugins and themes that comply with the WordPress coding standards remove all related files when uninstalled. In this case, they only need to be removed from the “Appearance” or “Plugins” menu in the WordPress admin panel. However, keep in mind that simply deactivating a plugin or theme is not enough, they must also be removed by clicking the “Delete” button. If your plugin does not clean up space after deletion, then you can get rid of the associated database costs using one of the following methods.
OPTIMIZE TABLES IN PHPMYADMIN
You can run a database optimization query directly from the phpMyAdmin interface. PhpMyAdmin is an application that allows you to access your raw database. You can access phpMyAdmin from your hosting account’s cPanel under the Databases menu.
In phpMyAdmin, open the database that belongs to your WordPress site and check the tables you want to optimize. You can also select the Check All option as shown in the image below:
Here, scroll through the drop-down list that is located below the tables. In the Table Maintenance group, select the Optimize Table option and run the query.
The Optimize Table option runs the OPTIMIZE TABLE command on your database, which according to the MySQL documentation:
Reorganizes the physical storage of table data and associated index data to reduce storage space and improve I/O efficiency when accessing a table.
Besides OPTIMIZE TABLE, you can also run the REPAIR TABLE query from the same phpMyAdmin menu, which “repairs a possibly corrupted table.
RUN THE WORDPRESS DATABASE OPTIMIZER TOOL
WordPress Core also has a built-in database optimization tool. It is disabled by default, however you can enable it by editing the wp-config.php file. You can find wp-config in the root directory of your WordPress installation on your server. Open it in a code editor and add the following option to the beginning of the file:
define( 'WP_ALLOW_REPAIR', true );
You can find the optimization script at http://your-site.com/wp-admin/maint/repair.php where you need to replace your-site.com with your own domain.
Here you can choose one of two options:
Database recovery – finds and fixes common database problems,
Database Repair and Optimization – Apart from all the tasks Repair Database performs, it also tries to optimize your database for better performance.
In most cases, the first option is sufficient, but if you need a deeper cleaning, choose the second. Wait for the tool to repair and/or optimize your database, then remove the setting you added to the wp-config file (otherwise anyone on the internet will be able to see sensitive information about your database).
USE DATABASE CLEANING PLUGIN
If you don’t want (or can’t) access your original database and wp-config file, or you want to perform advanced database optimization tasks, you can also use a plugin. Below we have compiled three of the best database cleaning plugins for you.
Please note that you should not use more than one database optimization plugin at a time, as these plugins have similar features that may interact with each other in unexpected ways.
WP-Optimize is a free plugin that takes care of all types of optimization on your WordPress site. Apart from cleaning your database, it also compresses your images and caches content on your site. Its database cleanup function:
The plugin is updated frequently and the support team is quick, so this is a good and safe solution if you want to run automatic cleanup on a regular basis.
Advanced Database Cleaner is another free database optimization plugin that has many advanced options. It allows you to remove all kinds of unused, obsolete and repetitive content types, for example:
You can also schedule and run automatic cleanups, view and clear tables, and display and preview items before deleting them. In addition, Advanced Database Cleaner supports installation from multiple sites.
Optimize Database after Deleting Revisions is a database optimization plugin that allows you to clean up your database with one click. If you want a simple solution that doesn’t require much configuration, optimizing your database after removing revisions may be the best choice for you. With this plugin you can remove:
It also supports WordPress Multisite and adds an “Optimize Database” link to the top admin panel, with which you can run database optimization with one click at any time.