A Detailed Look Into WordPress Database Optimization

by in WordPress on 19th May 2010 · Comments

It is a known fact that WordPress is powered by PHP and depends on MySQL database; in fact, all the content, including the website settings, are stored in it. When your WordPress site is visited, the web server will query the database to retrieve all the necessary information to display; a typical operation for most of the dynamic site. However, over time, the speed required for querying the database will increase due to the number of operations such as insert, updates, and the delete of data entries.

To restore to the original fast and smooth communication between your web server and the database, housekeeping for your WordPress database needs to be done. This is necessary to keep it running at its peak.

WordPress Database Optimization


Remember that it is crucial for you to backup your database before you embark on any dealings with the database.

WordPress Database Statistics

It's always good to find out what the current status of your database before you begin optimizing it. This will help you to keep check on the before and after status once the optimization of your database is completed.

Output the no. of Database Queries

One simple way to discover the number of WordPress query outputs and the time it takes to complete is to use the following WordPress function:

<?php echo get_num_queries(); ?> queries in <?php timer_stop(1,3); ?> seconds

Debug Queries

It may not be sufficient to only know the number of WordPress query outputs in order for you to troubleshoot and optimize your database. You will also needs the Debug Queries plugin to help you find out what are the queries being executed when the page renders.

Debug Queries
Debug Queries

Once you know your WordPress database statistics, you can now start to optimize your database. Analyze the queries to determine which plugin is causing extra queries that slow down your site. If you discover that a particular plugin is resulting in too many queries, you might want to reconsider a substitute plugin that does the same task.

Optimize Database Tables

As a start, try optimizing the overheads on your database as reported in phpMyAdmin. Overheads are usually caused by too many insert, update and delete activities on your database tables. When this occurs, you need to run Optimize Table manually via phpMyAdmin or by using WordPress Plugin. This will help reclaim the disk space and defrag your database tables.

WordPress Plugin

If you are not familiar with or find it troublesome to login to phpMyAdmin to optimize the overheads of your database, you can chose to do so with WordPress Plugin. These plugins will allow you to select the tables you wish to optimize and will also show you the total size of your database and how much of it can be reduced after you have completed the optimization.

Optimize DB
Optimize DB

WP Optimize
WP Optimize

phpMyAdmin

Using phpMyAdmin is the easiest way to optimize the overheads of your database without adding plugin load to your WordPress site. Simply login to your phpMyAdmin and select the option 1: Check tables having overhead . Then at the side in the drop down box, select 2: Optimize Table.

phpMyAdmin

Edit wp-config.php File

There are a few configurations you can declare in wp-config.php so as to enable easy WordPress database maintenance or optimization.

Empty your Trash

WordPress 2.9 has added a new functionality for the posts, pages, and the comments. Instead of permanently deleting these, you can now trash it and then later empty the trash, in case you might want to restore it. By default WordPress will empty the trash every 30 days, but you can change the time limit to 10 days simply by entering the following code in your wp-config.php.

define( ‘EMPTY_TRASH_DAYS’, 10 );

Repair your Database

WordPress 2.9 also introduces another feature that enables your database to be automatically repaired periodically. To setup the automatic database repair option, simply add the following line in wp-config.php.

define('WP_ALLOW_REPAIR', TRUE);

If you want to manually repair and optimize the database, you can visit: http://www.yourwpsite.com/wp-admin/maint/repair.php to do so.

Turn off Post Revision

With WordPress 2.6, there is a new feature that enables you to have post revisions, just in case you need to restore to the previous version. However, one issue for this feature is the number of extra records being inserted into the WordPress database. Over time, your database will be cluttered with plenty of redundant post revision entries. If you do not need this feature, you can easily turn it off by adding the following line to the wp-config.php file.

define('WP_POST_REVISIONS', FALSE);

SQL Queries for WordPress

In our previous article, 13 Useful WordPress SQL Queries You Wish You Knew Earlier we covered several SQL queries that can help you trim down your WordPress database.

SQL Queries

For easy reference, I have listed the queries below.

Delete Post Revision

DELETE a,b,c FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

Delete all Pingback / Trackback

DELETE FROM  wp_comments WHERE  comment_type = 'pingback';

Note: To delete trackback, change the pingback to trackback in the above SQL statement.

Delete all Spam Comments

DELETE FROM  wp_comments WHERE  comment_approved = 'spam';
  • 0 = Comment Awaiting Moderation
  • 1 = Approved Comment
  • spam = Comment marked as Spam

Delete Post Meta

DELETE FROM  wp_postmeta WHERE  meta_key = 'your-meta-key';

If the following standard WordPress meta keys are taking up too many entries in your wp_postmeta table, you can try to do a cleanup from time to time.

  • _edit_lock, _edit_last - Prevent another user from editing the same post at the same time.
  • _wp_old_slug - Remembers that old slug even after you changed it.

Above is an overview of the SQL queries you can use when you are optimizing your WordPress Database. For more details, you can read more on our previous article - 13 Useful WordPress SQL Queries You Wish You Knew Earlier.

Clear Away Unused Table in Database

Very often, you test out a plugin that might seem useful only to realize that it’s something you do not need at a later stage. In the process, some of these plugins create new table(s) in your database to store extra information. However, even after you’ve deactivated and deleted the plugins from your folder, those extra tables of data are still left in your database. Delete the extra tables if they are not in use.

However, be careful not the delete the essential tables. Below is the list of standard WordPress tables that you should NOT delete.

WordPress Tables

Clear Away Unused Entries in wp_options Table

Most plugins need to store bits of data in the database, especially those that have an option page and allow users to make changes; it is unavoidable. All these settings data can be easily stored in the wp_options table. So what happens after you have deactivated and deleted away the plugins? All those settings are left behind. Over time, if you have been trying out quite a number of plugins, the number of entries and the size of your wp_options table will shock you. To minimize this issue, remove the entries that are no longer needed.

It is a tedious task to go through all the entries in the wp_options table to identify the additional entries that is a result of the deleted plugin. Also, if you delete the wrong entry, your site might end up falling apart.

Here we have a plugin that can assist you to identify redundant entries in your wp_options table.

WordPress Clean Options Plugin
WordPress Clean Options is a plugin that can help identify redundant entries in your wp_options table. The plugin will identify options that are no longer being used by your WordPress site by analyzing whether there is any link to the options. Once the plugin has scanned through the wp_options table, you will be presented with the list of all the orphaned options.

Clean Option

However, don’t be complacent. You will still have to look at it individually and carefully. Tick the ones that you really don't need any more to remove it.

Cache Your Database

After the database housekeeping for your WordPress site, the final step is to cache your database if you have not done so. This helps to reduce the amount of SQL queries from being called to the server by caching the queries to the database which will speed up the performance of the WordPress site.

DB Cache Reloaded
Cache Reloaded

Optimize Now!

We hope that the WordPress database optimization tips we have shared with you in this article will be able to help you optimize your WordPress database to reduce the size and speed up your database operation. This maintenance process should be an ongoing one. Schedule your maintenance at least once a month to ensure that your WordPress database is always running in peak condition.

If there are more WordPress database optimization tips that I have not covered, do share with us your tips in the comment box below.

Terrance is a versatile web developer and the technical editor at OXP. He enjoys creating functional websites and is particularly engrossed in all the tiny details mixed together to construct great user experiences. He always believe that every web user deserves the best!