Blog

Your WordPress database stores all the content for your website. This includes blog posts, pages, custom post types, comments, links, form entries and portfolio items. It also stores all your website, theme and plugin settings.

If you update your website regularly, your database will grow over time. A large database will adversely affect the performance of your website because it takes longer for your server to retrieve information from database tables. This is why database optimization is important.

By removing unnecessary data, you can considerably improve the efficiency of your database and make your site pages load quicker.

Understanding the WordPress Database

If you are using WordPress to publish content, it is in your best interest to have an understanding of the core WordPress database tables; particularly if you are planning on optimizing your WordPress database.

WordPress currently has 11 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 11 tables listed below. All other tables in your database were created manually or created by a WordPress theme or plugin.

Let’s look at what each database table stores:

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–most database management tools have a similar interface and work in the same way.

You can also manage your WordPress database using a plugin such as Adminer (formerly known as phpMinAdmin). Although a database WordPress plugin can make accessing your database simpler, I advise against managing your database this way becuase of the associated security risks. If you have a plugin such as Adminer installed, and an unauthorized person gained access to your database, they could do anything they wanted to your website.

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 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 something of a gray area).

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'

Using phpMyAdmin, you can 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 click on the “Go” button.

Once you have optimized your WordPress database, phpMyAdmin will confirm that your tables have been optimized.

Another useful option here is “Repair table.” Repairing a table will help you fix a table that has become corrupted.

WordPress also 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://yoursite.com/wp-admin/maint/repair.php.

The optimization tool will attempt to repair each table within your database. From time to time, the script may not be able to repair certain tables–that’s perfectly normal. 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 obvious downside to this method is that anyone can access your script and execute it. As a result, you should remove the WP_ALLOW_REPAIR line from your wp-config.php file after you have finished using 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, in a few simple steps, you can greatly reduce bloat, or even eliminate bloat entirely from your website.

Below are some of the most common sources of bloat found in a WordPress database.

Revisions

The WordPress revision system makes many WordPress databases unnecessarily large. First introduced in WordPress 2.6, the revisions feature stores a copy of every draft and update of blog posts in your database. It is a useful feature because 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.

While I am a fan of the revisions feature, there aren’t any real benefits 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. So, 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.

Additionally, 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 also be removed from the optimization process.

This plugin 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.

Another option for deleting post revisions is Better Delete Revision. It provides a list of all revisions saved on your website. Unfortunately, there is no option to delete revisions individually; there is only an option to delete all revisions at once. The plugin does, however, have an option for optimizing all of your database tables to reduce overhead.

Autosaves

WordPress 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 the autosave feature because autosave saves multiple copies of your posts and pages. This is not true. Autosave only ever saves one copy of your article and does 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 accidentally. Or if your computer/browser crashes. 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 easily take up hundreds or even thousands of rows in your wp_coments table.

A good anti-spam plugin will stop most spammers in their tracks so 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 also be deleted automatically).

Spammers tend to target older articles that have good rankings in search engines. As a result, you can 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 yet another fail safe system from WordPress that prevents you from deleting items accidentally. Should you wish, the system allows you to restore items that are currently sitting in the trash folder.

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 something else).

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 completey disabled by adding the following line of code to your wp-config.php file:

define( 'EMPTY_TRASH_DAYS', 0 ); // Zero days

Disabling the trash system is a terrible idea because you will lose the ability to 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 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

Over 99% of all WordPress plugins store settings and data in your WordPress database. Unfortunately, when you uninstall a WordPress plugin, this information is left sitting in your database.

This is by design. If data was removed every time you deactivated a plugin, you would have to configure the plugin again whenever you reinstalled and reactivated 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.

As a result, 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, 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.

A useful plugin to help you with this is WPDBSpringClean. The plugin will identify unused tables from uninstalled plugins and give you the option to delete them.

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.

A great tool for checking what WordPress plugins are slowing down your website is P3 (Plugin Performance Profiler). The plugin will highlight the impact that each plugin adds to your page loading time.

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.

WP Clean Up is a great way of quickly removing unwanted data. It allows you to remove revisions, drafts, comments, and more – all at the touch of a button.

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 can optimize your database through your ManageWP Dashboard.