Do you want to optimize WordPress database to speed up your website? We will talk in detail about the WordPress database, and its relationship with website speed, and show you 5 effective ways to optimize WordPress database to speed up your website. So, keep reading if you want to do all these.
The database is where you store all your website data including your posts, comments, login information, theme, configuration settings, and more. So, the database has a wider spectrum of what you showcase on your website. With time your website database stores a lot of data that you don’t need. But every time you load your website, your website engine processes all those data and gives you only what you need or ask for. That’s why your website may take longer than usual time to load.
Many WordPress users like you are facing slow page loading issues due to uncleaned and cluttered databases. The solution is you need to optimize WordPress database. Let us show you how.
WordPress Database Structure
Database works behind the scene and end users don’t see anything about it. But when you are adding a lot of plugins and making a lot of changes, optimizing the database is something you must do. It is better to have an idea about the database structure before you start optimizing them.
The database structure and the diagram were last updated in WordPress v4.4. The only database supported by WordPress is MySQL version 5.0.15 or greater, or any version of MariaDB. The diagram gives a visual overview of the WordPress database and the relations between the tables created during the fresh WordPress installation. Let’s take a look at the database diagram here.
Default Database Tables in WordPress
By default, the WordPress database has 12 tables (as last updated in WordPress v4.4). The plugins and themes you install on your WordPress add more to the default list. The default database table in WordPress is just like below.
| Table Name | Description | Relevant Area(s) of WordPress User Interface |
|---|---|---|
| wp_posts | Each post features information called metadata and it is stored in the wp_postmeta. | Administration > Posts > Add NewAdministration > Posts > PostsAdministration > Pages > Add NewAdministration > Pages > PagesAdministration > Media > Add NewAdministration > Media > LibraryAdministration > Appearance > Menus |
| wp_comments | The comments within WordPress are stored in the wp_comments table. | Administration > Comments > Comments |
| wp_commentmeta | The core of the WordPress data is the posts stored in this. Also, Pages and navigation menu items are stored in this table. | Administration > Comments > Comments |
| wp_users | The list of users is maintained in table wp_users. | Administration > Users |
| wp_options | Each comment features information in the metadata and it is stored in the wp_commentmeta. | Administration > Settings > GeneralAdministration > Settings > WritingAdministration > Settings > ReadingAdministration > Settings > DiscussionAdministration > Settings > PrivacyAdministration > Settings > PermalinksAdministration > Appearance > Widgets |
| wp_links | The wp_links holds information related to the links entered into the Links feature of WordPress. (deprecated) | Administration > Links > Add NewAdministration > Links > Links |
| wp_postmeta | Each term features information called metadata and it is stored in wp_termmeta. | Administration > Posts > Add NewAdministration > Pages > Add New |
| wp_terms | The categories for both posts and links and the tags for posts are found within the wp_terms table. | Administration > Posts > Post TagsAdministration > Posts > CategoriesAdministration > Links > Link CategoriesAdministration > Posts > Add NewAdministration > Posts > PostsAdministration > Pages > Add NewAdministration > Page > Pages |
| wp_termmeta | Each term features information called metadata and it is stored in wp_termmeta. | |
| wp_term_relationships | Each term features information called meta data and it is stored in wp_termmeta. | |
| wp_term_taxonomy | This table describes the taxonomy (category, link, or tag) for the entries in the wp_terms table. | |
| wp_usermeta | Posts are associated with categories and tags from the wp_terms table and this association is maintained in the wp_term_relationships table. The association of links to their respective categories is also kept in this table. | Administration > Users |
How to Optimize WordPress Database for Speed
No matter how cluttered or messed up your database is, you have ways to optimize it in WordPress. We are going to discuss some proven ways to optimize WordPress database easily. Let’s begin!
Note: Always take a backup of your database before you run a cleanup! Once a database optimization is performed, there is no way to revert it.
Delete and Limit Post Revisions
Every time you update your post (all post types – posts, pages, and everything like this), your database saves a version of the post. It becomes a mess when you have updated posts repeatedly and a great number of times. In fact, you never need all these revisions, maybe some recent versions will be helpful to get back to.
It is best to delete the unnecessary post revisions and keep only the necessary ones.
How to Delete Post Revisions In WordPress
Post revisions take unnecessary storage meaning you may be spending extra money on your hosting provider due to this. And, also they hurt your website performance. Post revisions could be the reason your pages are slow to load. You need to delete post revisions and limit post revisions to a certain number. Here’s how you can do that.
If you are using WP Rocket caching plugin, you can delete post revisions easily. Go to your WordPress Dashboard > Settings > WP Rocket, and click on Database. You will find options to clean up Revisions, Auto Drafts, and Trashed Posts.
Click on the items you want to delete and save the changes by clicking on the SAVE CHANGES button at the bottom.
Note: Upon enabling these options, post revisions and drafts will be permanently deleted. You don’t need to enable these options if you need to retain revisions or drafts.
Also, you can delete post revisions with Perfmatters, a WordPress performance optimization plugin. While you have Perfmatters installed on your website, go to your Dashboard > Settings > Perfmatters > Tools > Database,
You will find the toggle with the number of posts revisions in your entire website. Now enable the toggle and click on “Optimize Now “at the bottom.
How to Limit Post Revisions in WordPress
You can limit post revision in WordPress with a plugin. The Perfmatters plugin can help you limit post revision in WordPress. You can limit the revision number to 1/2/3/4/5/10/15/20/25/30. This would be enough.
How to Limit Post Revision In WordPress with a WordPress Plugin
While you have Perfmatters installed and activated on your site, go to your Dashboard > Settings > Perfmaters > General. Scroll down to the “Limit Post Revisions” option and select the number of revisions you want to have from the available options.
Save the changes by clicking on the button “Save Changes” at the bottom to limit post revision in WordPress.
Apply VIRFICE as a coupon code to get a 20% discount on Perfmatters plans
Also, You can limit post revisions manually in WordPress. Find your wp-config.php file in your (FTP depending on your host). Open your wp-config.php file, and add the following code:
define('WP_POST_REVISIONS', 3); Here 3 is the number of revisions you want to keep. You can have any number you want.
Delete Spam and Trashed Comments
It is very common for people to leave spam comments on your posts. Whether you don’t approve them or trash them, those comments stay in your database. Anti-spam plugins like Akismat help you detect them as spam, but they remain in your database. It is best to delete them permanently and clean up your database.
You can do that in the WP Rocket plugin too. Go to Dashboard > Settings > WP Rocket, and click on Database.
Find the “Comments Cleanup” checkmark on Spam Comments and Trash Comments, and save the changes at the bottom to permanently delete spam and trash comments on WordPress sites.
Delete Transients
Transients are a portion of the WordPress codebase that allows plugin and theme developers to store cached data. They are often used to cache data from external API requests that might introduce latency. Each transient has a timeline to expire, after which it will automatically be removed from the database.
Transients hurt site performance as long as they are not deleted. You can delete transients easily in WP Rocket caching plugin. And, it is safe to remove them.
Go to Dashboard > Settings > WP Rocket, and click on Database. Find “Transients Cleanup”, Checkmark on “All transients” and click “SAVE CHANGES” at the bottom.
Optimize Database Tables
WordPress has 10+ tables in the database by default. The plugins and themes you install add more tables to this list. Some of the tables you add to your website are unnecessary. You can easily delete them and clean up your database to speed up your WordPress site.
As we are showing most of the things in the WP Rocket plugin. The process is similar, Go to Dashboard > Settings > WP Rocket, and click on Database.
Find “Database Cleanup”, Checkmark on “Optimize Tables” and click “SAVE CHANGES” at the bottom.
Clean Up WordPress Database Regularly
You have optimized your WordPress database manually so far. But optimization is a repeated activity of your website maintenance. That is why it is important to optimize WordPress database regularly.
You can schedule regular database cleanup in WP Rocket. Go to Dashboard > Settings > WP Rocket, and click on Database.
Find “Automatic Cleanup”, Checkmark on “Schedule Automatic Cleanup” and select the frequency of your automatic database optimization. You can select any option from Daily, Weekly, and Monthly. Click “SAVE CHANGES” at the bottom once done.
How to Backup WordPress Database
Whenever you are thinking about optimizing your WordPress database, it is always recommended to take a backup so that you can get back to it if something breaks or goes missing.
So, let us show you how to back up WordPress database in some easy steps. We will show you the steps using phpMyAdmin. And with database backup, we only mean all your posts, pages, comments, and everything that your database includes. Backing up files and folders such as images, and theme files falls into the category of WordPress site backup. We are not doing that here today.
Step 1: Log into phpMyAdmin on Your Server
Select your WordPress database from the left-side window. Here “wp” is the name of the database in the image.
The window on your right will show you all the tables inside your WordPress database. Click the ‘Export’ tab on the top. Take a look at the image above.
Step 2: Select the Option Quick and Click Go
Make sure you select the “Quick” option and click ‘Go’. You will be prompted for a file to download. Save the file to your local computer.
Congratulations! You have successfully taken a backup of your WordPress database. Apart from this, you can take custom backups too. Instead of selecting the “Quick” option in Step 2, you can select “Custom” and take a backup as you want.
Best WordPress Plugin to Optimize WordPress Database for Speed
There are many ways you can optimize WordPress database for speed. You can do it manually if you know WordPress development stuff. Otherwise, you can depend on any optimization plugin to clean up the WordPress database. Here are some best WordPress plugins to clean up the WordPress site database. Let’s take a look.
WP Rocket – the Best WordPress Caching Plugin Capable of Optimizing Database
You already have noticed we have mostly showcased the database optimization process with the WP Rocket plugin. Database optimization is a big part of website speed. That is why WP Rcoket did not exclude database cleaning in the process.
WP Rocket can optimize your WordPress site database including cleaning post revisions, spam comments, transients, and more. Read our detailed WP Rocket review to know what else is this plugin capable of.
Perfmatters – Lightweight Performance Plugin to Speed up WordPress Site
Perfmatters is one of the top choices when it comes to optimizing websites for speed. The plugin took database optimization into consideration deeply and has every option to optimize the database easily.
Apply VIRFICE as a coupon code to get a 20% discount on Perfmatters plans
Apart from deleting post revisions, Perfmatters lets you limit post revisions to a certain number so that you can have some versions of the posts saved in your database to get back to.
Also, Perfmatters has plenty of other performance optimization options that would help you speed up your website.
WP-Optimize – The WordPress Performance Plugin
WP-Optimize is an all-in-one WordPress performance plugin that caches your site, cleans your WordPress database, and compresses images in your WordPress site. The plugin takes care of your website performance.
It helps you remove unnecessary data from your database and make your website database clean. WP-Optimize can optimize a website without the need for running manual queries. Apart from WordPress database optimization, it has a lot of other options too.
Optimizing a database in WordPress is not that difficult if you use a plugin. In our case, we have showcased the process with WP Rocket. Whether you use a plugin to optimize WordPress database or do it manually, make sure to keep a backup before you start.
We hope our tutorial has helped you with database optimization and speeding up your website. Do visit our site for more such tutorials and subscribe to our newsletters to get regular updates. Good luck!
