Your WordPress database stores all of your website content. This includes blog posts, pages, comments, and custom post types such as links, form entries, and portfolio items. It also stores your website settings, theme settings, and plugin settings.
If you update your website regularly, your database will grow larger over time. A large database can greatly affect the performance of your website as it takes longer for your server to retrieve information from database tables. This is why database optimization is so important.
By removing unnecessary data, you can improve the efficiency of your database and make your web pages load quicker. Let us look at how this can be achieved.
- Understanding the WordPress Database
- How to Optimize and Repair Your WordPress Database
- How to Remove Bloat From Your WordPress Database
- How to Optimize Your Database Using a WordPress Plugin
- Final Thoughts
Understanding the WordPress Database
If you are using WordPress to publish content on the internet, I believe it is in your benefit to have an understanding of the core WordPress database tables; particularly if you are planning on optimizing your WordPress database.
WordPress currently has 12 core tables (this could, of course, change in a future version of WordPress). Most WordPress websites have dozens of tables because plugins save settings and other data in the WordPress database. Themes may also save settings and other data in your database.
If you check your own database, you will see the 12 tables listed below. All other tables in your database were created manually or created by a WordPress plugin or a WordPress theme.
Let’s look at what each database table stores:
- wp_commentmeta – Stores meta-information about comments
- wp_comments – Stores your comments
- wp_links – Stores blogroll links
- wp_options – Stores the options defined in the admin settings area
- wp_postmeta – Stores post meta information
- wp_posts – Stores data for posts, pages, and other custom post types
- wp_terms – Stores post tags and categories for posts and links
- wp_termmeta – Stores term information called metadata
- wp_term_relationships – Stores the association between posts and categories and tags and the association between links and link categories
- wp_term_taxonomy – Stores a description about the taxonomy (category, link, or tag) used in the wp_terms table
- wp_usermeta – Stores meta-information about users
- wp_users – Stores your users
Check out the database description page on WordPress.org for more information about WordPress core tables.
How to Optimize and Repair Your WordPress Database
phpMyAdmin is the most common way to manage a WordPress database. If you are not using cPanel as your hosting control panel, your hosting plan may be using a different MySQL management tool to phpMyAdmin. Do not be too concerned about this as most database management tools have a similar interface and work in the same way.
You can also manage your WordPress database using a plugin. Although a database WordPress plugin can make accessing your database simpler, I would advise against managing your database in this manner as it is a large security risk.
If you check your database, you will see two columns at the end: size and overhead. The size of a table depends on the amount of data that is stored in it. If more rows are stored in a table, the size of the table increases.
Overhead is a temporary disk space that is used by your database to store queries. Over time, a table’s overhead will increase.
It is perfectly normal to have overhead in your WordPress database and it should not affect performance unless overhead gets high (though the actual amount of overhead that should be considered too high is a grey area).
* Please note that the above screenshot is taken from a new WordPress installation I created for screenshots. That is why the database prefix is still wp_. To make your website more secure, always change your WordPress database prefix through wp-config.php to something different.
Optimizing your database will remove the overhead and reduce the overall size of your database. Many developers note that optimizing a database is akin to defragmenting a hard drive.
Every database will, over time, require some form of maintenance to keep it at an optimal performance level. Purging deleted rows, resequencing, compressing, managing index paths, defragmenting, etc. is what is known as OPTIMIZATION in mysql and other terms in other databases. For example, IBM DB2/400 calls it REORGANIZE PHYSICAL FILE MEMBER.
It’s kind of like changing the oil in your car or getting a tune-up. You may think you really don’t have to, but by doing so your car runs much better, you get better gas mileage, etc. A car that gets lots of mileage requires tune-ups more often. A database that gets heavy use requires the same. If you are doing a lot of UPDATE and/or DELETE operations, and especially if your tables have variable length columns (VARCHAR, TEXT, etc), you need to keep ‘er tuned up.
You can optimize tables that are affected by overhead by using the SQL command OPTIMIZE TABLE. For example, you could optimize the wp_posts table by executing this SQL query:
OPTIMIZE TABLE 'wp_posts'
There is no need to use an SQL command as phpMyAdmin allows you to optimize tables from the main drop down menu. All you need to do to optimize your database is click on the “Check All” box, select “Optimize table” from the dropdown menu, and then click on the “Go” button.
Once you have optimized your WordPress database, phpMyAdmin will confirm that your tables have been optimized.
Another useful option you should keep in mind for the future is the “Repair table”. Repairing a table will help you fix a table that has become corrupted.
WordPress has a tool that allows you to repair and optimize your database. You can find out more about this tool in the Automatic Database Optimizing section of the WordPress.org guide on wp-config.php.
To use the optimization tool, you first need to add this line to your website wp-config.php file.
define( 'WP_ALLOW_REPAIR', true );
Once you have added the above line to wp-config.php and saved the file, you can access the optimization tool at http://www.yourwebsite.com/wp-admin/maint/repair.php.
The optimization tool will attempt to repair each database table. From time to time, the script may not be able to repair certain tables.
If you do not successfully repair your database in the first attempt, simply run the optimization tool again.
If you select “Repair and Optimize Database”, WordPress will optimize every table that has not already been optimized.
You do not need to be logged in to run the WordPress optimization tool. The downside to this is that anyone can access your script and execute it. Due to this, you need to remove the WP_ALLOW_REPAIR line from your wp-config.php file after you have used the optimization tool.
How to Remove Bloat From Your WordPress Database
Most WordPress databases store a lot of unnecessary data. This additional bloat makes websites slower and less efficient.
There are a number of things that add bloat to your website database. However, by following good practices, you can greatly reduce bloat, or even eliminate bloat entirely from your website.
Let’s take a look at the main causes of bloat in a WordPress database.
Revisions
The WordPress revision system makes many WordPress databases unnecessarily large. First introduced in WordPress 2.6, the feature stores a copy of every draft and update of your blog posts. It is a useful feature as it allows you to revert back to older copies of articles and check earlier drafts.
Unfortunately, WordPress places no limitation on the number of revisions that are saved. If you are working on a long article, this could result in hundreds of revisions being saved. Even though the published article will only take up one row in your database, the corresponding revisions could use dozens or hundreds of rows in your database.
I am a big fan of the revision system, however, I do not believe there is any real benefit to saving an unlimited number of revisions for each blog post. Thankfully, WordPress allows you to easily reduce the number of revisions that are stored.
To reduce the number of revisions that are saved, simply add the following code to your wp-config.php file.
define( 'WP_POST_REVISIONS', 2 );
Post revisions can be completely disabled by adding the following code to your wp-config.php file.
define( 'WP_POST_REVISIONS', false );
I would advise against disabling post revisions completely. While disabling post revisions will undoubtedly reduce the size of your database, it removes the fail-safe system that revisions provide. Therefore, in the event of you closing your browser in error or losing your internet connection, you could lose everything you worked on since the last save of your draft.
Reducing the number of revisions that are saved, or disabling post revisions altogether does not affect the revisions that are already saved. Therefore, post revisions attached to older blog posts will still be stored in your database.
Once an article has been published, there is little need to keep older post revisions, therefore you may want to consider removing all revisions from published articles. There are a number of WordPress plugins that allow you to do this (you can also remove revisions using MySQL; however you should be aware that problems can occur if you do not use the correct command).
For example, I used the plugin Optimize Database after Deleting Revisions earlier this year to reduce the size of my blog database by 59%.
The plugin allows you to define the number of revisions that are saved. It also lets you delete trashed items, spam items, unused tags, and expired transients. Specific database tables can be removed from the optimization process.
Optimize Database after Deleting Revisions has a scheduler too. It can be used to optimize your website automatically once an hour, twice a day, once a day, or once a week.
I would also like to briefly speak about autosaves. The WordPress autosave feature saves one autosave of your article every 60 seconds. This interval can be changed by adding the following code to your wp-config.php file.
define( 'AUTOSAVE_INTERVAL', 160 ); // Seconds
A lot of bloggers have advised disabling autosave as autosave saves multiple copies of your posts and pages. That is simply not true. Autosave only ever saves one copy of your article and will not use up much room in your database.
The autosave feature is an important fail-safe that will help you if you lose your internet connection or close your browser by mistake. The feature does not use up much space in your database; therefore I encourage you to keep it activated.
Spam Comments
If your website receives a lot of spam, you may find that spam comments are taking up a lot of space in your database. By default, spam comments are automatically deleted after 30 days; however, during that time they can take up hundreds or even thousands of rows in your wp_coments table.
A good anti-spam plugin can stop many spammers in their tracks so that the volume of spam comments you receive is reduced.
Akismet is a good solution as it allows you to discard obvious spam so that the comment is deleted from your database right away (though be concious of the fact legitimate comments could be deleted automatically).
There is less need to use a plugin now as WordPress added an “Empty Spam” button to the spam comment page that allows you to permanently delete all spam comments instantly.
Spammers tend to target older articles that have good rankings in search engines. You can therefore reduce the amount of spam that your website receives considerably by disabling comments on articles that are older than a specified number of days. This setting is located in the discussion settings page in the WordPress admin area.
Spam comments can also be deleted using the following SQL command.
DELETE FROM wp_comments WHERE comment_approved = 'spam'
All comments awaiting approval can be deleted by using the following SQL command.
DELETE FROM wp_comments WHERE comment_approved = '0'
Since you can now delete all spam using the “Empty Spam” button displayed in the spam comment page, there is no real benefit to using an SQL query to delete spam from your database.
Deleted Items
Whenever you delete an item in WordPress, such as a blog post, page, image, comment, or link; it is sent to the trash folder. This is another fail-safe system from WordPress that prevents you from deleting items by accident. Should you wish, the system allows you to restore items that are currently sitting in the trash.
Unless you are deleting many items regularly, you should not need to worry about the space deleted items take up in your database. However, it is worth understanding how the trash system works, particularly if you have to delete hundreds or thousands of items from a WordPress website (whether it be posts, comments, images, or whatever).
Deleted items will continue to be stored in your database until the trash is emptied. By default, trash items are permanently deleted after 30 days.
The number of days before trash is emptied can be changed by adding the following code to your wp-config.php file.
define( 'EMPTY_TRASH_DAYS', 5 ); // 5 days
The trash system can be completely disabled by adding the following line of code to your wp-config.php file.
define( 'EMPTY_TRASH_DAYS', 0 ); // Zero days
I do not believe that disabling the trash system is a good move as it means that you cannot restore any items that were deleted by mistake. It is more practical to simply reduce the number of days before trash is deleted.
WordPress Transients
WordPress Transients offer developers a way of storing data temporarily in the WordPress database. Transient records are stored in the WordPress options table.
Expired transient records can add bloat to your database and make your website run slower. There are a number of plugins that help you manage your transients and delete expired transient records that are no longer needed.
The WordPress plugin Transient Cleaner has an option to delete expired transients and remove all transients. Delete Expired Transients also offers this functionality and the plugin allows you to set up a daily task for deleting expired transients.
Transients Manager is one of the best solutions for viewing your transients. It allows you to view, edit, and delete transients. However, the plugin does not have any options for bulk deleting expired transients.
Transients are not something you need to worry about on a regular basis, however, it is worth checking them periodically to verify they are not affecting performance.
Unused Plugin and Theme Tables
99% of all WordPress plugins store settings and data in your WordPress database. Unfortunately, when you uninstall a WordPress plugin, this information is not removed.
This is by design. If data was removed every time you deactivated a plugin, you would have to configure the plugin again when you reactivate it. You would also lose any reports or content that the plugin generated.
However, if you have decided to stop using a plugin, or if you were simply testing a plugin, you will want to remove all data when you uninstall the plugin. A small number of WordPress plugins include an option on their settings page to remove all data, though the majority of plugins do not have this option.
Due to this, WordPress databases can accumulate a lot of additional bloat over time. It is not uncommon for a WordPress database to contain dozens of tables for plugins that were removed months ago, or even years ago.
WordPress themes also store settings in the WordPress database and these settings will remain in your database when you switch themes.
Unused tables can be removed from your database manually through a database management tool such as phpMyAdmin. However, even if you have a good understanding of the 11 core WordPress tables, you will find it difficult to distinguish tables from active plugins and tables from uninstalled plugins.
Plugins That Add Bloat
Each plugin you install on your website increases the size of your website database. The space that some plugins use in your database is negligible, but some WordPress plugins can add a lot of weight to your database.
Whenever you install a new WordPress plugin, you should review how much load it places on your server’s CPU and how much storage it uses in your database.
The following types of WordPress plugins are known for storing a lot of data in the database.
- Anti-Spam Plugins – In order to protect your website, many anti-spam plugins save information such as IP addresses and email addresses. For example, Akismet stores a lot of data in the WP_CommentMeta table.
- Security Plugins – Just like anti-spam plugins, security plugins keep track of a lot of information about spammers and hackers.
- Statistic Plugins – WordPress plugins that provide traffic and analytical reports need to store large amounts of data in your website database. This includes views, visits, countries, browsers, operating systems, referrers, keywords, and more.
- Related Posts and Popular Posts Plugins – WordPress plugins that showcase other posts are notorious for using a lot of CPU and a lot of database storage. These types of plugins need to store a lot of data in your database; such as the number of likes, shares, and views, that each page on your website has received.
- Link Tracking Plugins – Most link tracking solutions give you the option of tracking the number of clicks to your links. This is useful for understanding visitor habits and seeing where outgoing traffic is leading. Unfortunately, tracking hits can take up a lot of space in your database.
Some developers are conscious of the fact their plugins use a lot of storage. Which is why some developers include an option in their plugins to clear data.
For example, I track links using Pretty Link. The plugin allows you to disable the built-in tracking system. Three tracking setups are also available: Normal tracking, an extending tracking configuration that offers more stats but slower performance, and a simple click-tracking option that offers fewer stats but better performance.
Stats can also be deleted from within the settings area. The plugin allows you to delete all hits or hits within the last 30 or 90 days.
Other WordPress plugins allow you to reset data and remove the tables the plugin added. However, in general, most plugins do not feature an option to remove all data.
If a plugin is using a lot of storage in your database, or slowing your website down considerably, remove it. I would only recommend keeping a slow WordPress plugin installed if it was essential to a website’s success. However, I believe there are always alternative solutions available to WordPress users.
How to Optimize Your Database Using a WordPress Plugin
Throughout this article, I have given advice on how to optimize your database using phpMyAdmin and wp-config.php. If the thought of using phpMyAdmin worries you, you may prefer to use a WordPress plugin to optimize your database. There are a number of good options available.
The first option is to use our very own plugin, Hummingbird.
In addition to a vast suite of award-winning WordPress optimization features, Hummingbird includes a Database Cleanup tool (Hummingbird > Advanced Tools > Database Cleanup) with automatic cleanup scheduling and helpful tooltips that let you know what type of unnecessary data you are deleting from your WP database.
WP-Optimize can be used to remove post revisions, drafts, spam comments, unapproved comments, comments in the trash, transient options, pingbacks, and trackbacks. It also includes a page that shows the data size, index size, and overhead, of each database table.
Another popular optimization WordPress plugin is WP-DBManager. It is a feature-packed plugin that lets you optimize and repair your database. Automatic backups of your website can also be configured.
WP-DBManager is a good plugin, but from a security point of view I would be careful using it as the plugin also allows you to empty tables, drop tables, and run MySQL queries. Therefore, anyone who gained access to your website using malicious methods would be able to do a lot of damage.
Those of you who use ManageWP to manage multiple websites can optimize your database through your ManageWP Dashboard.
Final Thoughts
I have helped many website owners over the years with WordPress-related problems. Sadly, very few people appear to take steps to keep their website database running efficiently. There really is no excuse for this as it only takes a few minutes to configure a WordPress website correctly.
This is what I do on my WordPress websites to keep their databases optimized:
- I reduce the number of post revisions to two by adding
define( 'WP_POST_REVISIONS', 2 );to wp-config.php - I employ strong anti-spam measures to greatly reduce the volume of spam added to my database
- I review any items I delete and then delete them permanently (rather than keeping items in the trash)
I also check my database periodically through phpMyAdmin. This gives me an opportunity to optimize the database tables and remove any unused database tables.
For some WordPress websites, I have reduced the number of days items stay in trash from 30 to 5. However, I usually do not take this step as I am in the habit of deleting items permanently.
To reduce the number of calls to my database, I install a cache WordPress plugin. This does not reduce the size of my database, but it puts less stress on my MySQL server and ensures my pages load quickly.
Image credits: Designmodo from www.flaticon.com is licensed by CC BY 3.0, Icon made by Freepik from www.flaticon.com is licensed under CC BY 3.0