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.
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.
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.
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.
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.
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.
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.
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.
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.
Very helpful information Terrance. Well written with easy to follow instructions and explanations. I didn't know much about optimizing my WordPress database. Hopefully this will help speed things up.
Very nice roundup. I also use these tool to optimize my database: phpMyAdmin, Clean Options plugin. I hate to have a heavy database, which can influence to the page loading time.
these are really helpful and useful tips. thanks for the post.
very nice!
lots of useful tips, thanks
Very well done. Must reiterate the third paragraph.
"Remember that it is crucial for you to backup your database before you embark on any dealings with the database."
very impressive topic and impressive information .
Thanks for this will share!
So, do you have any numbers on these actions? How does performance improved? How come you choose to cache DB-queries through a plugin rather than directly in the MySQL-server?
Here are some numbers on my development server with cache plugin enabled:
Before:
Total query time: 0.03939s for 31 queries.
After:
Total query time: 0.00405s for 3 queries.
As for caching it directly on MySQL server, any good way of doing it.?
Well, Query Caching is quite complex and while i suspect that WordPress developers do a lot of performance work and analyzing different querys with A/B-testing(http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html), it's not a sole answer to minimizing memory footprint and user download times. Often it's more prevalent to implement a gateway caching method such as an invisible proxy with the help of Squid and such. The problem with performance in wordpress is that it is very extensible and has to be backwards compatible a whole lot.
Some good resources here for speeding up the actual user experience, one way to attack the simultaneous download issue and javascript download blocking issue is to download and use the wordpress plugin "head cleaner" which moves javascript to the bottom of the source code and minifies both css and js. Oh, and don't forget to tag, expiredate and gzip all you content. It makes a great difference on second page load.
http://code.google.com/speed/articles/
http://developer.yahoo.com/yslow/
Stefan,
Plugins make it easy for the less expert among us at the cost of some overhead.
Awesome article, Terrance!
Just what I needed to get my WordPress install even faster. ;)
Excellent post! Learnt a few new exiting things :)
Some great advice here, I'll be trying some out this week! Spring clean for the database!
Its an very useful article to read. i had been searching for this for past one month. thanks for Terrance.
i had facing lot of problems regarding post revisions now i cleared it.
it's very good advices! i'll try to do some of that things on my blog. I knew many of that, but here also many new hooks. thanks!
Thanks for this advice, will come in handy.
Awesome and very useful tips! I am a bit nervous to go to my database lol
Some really good ideas here on optimizing the database. Usually Cache and compression tools do what I need. But you can't always cache your WordPress build into a static page.
The amount of size you will save in deleting revisions pales in comparison to the amount you will save if if you have had your WordPress blog up and running for a while and you delete "_transient_feed" (etc.) entries in the options table. It caches the entire RSS feed for the dashboard entries. It gets very large.
Forget the other tips. That should be #1.
Fantastic advice Thomas, I've been pulling my hair out trying to optimize my wordpress installs following some massive slow down problems. I put pretty much everything into place from the article (which is all GREAT advice everyone should heed - I only wish I found it before learning all about it on my own, piece by piece) and things improved but the sites were noticably slow. I pulled out the _transient_feed lines in the wp_options table and the site snapped right back to traditional performance.
How often do you recommend clearing out those lines? Should I also pull out the _transient_timeout_feed lines?
Thanks,
Drew
Nice info.
Can/should define(ENABLE_CACHE, true); be removed if I install DB cache reloaded?
Hi,
you don't really know HOW you helped me with this very nice POST.
I optimized 3 websites with this help.
Thank you very much and wish all best !!
Stefan
Nice one Terrance,
I am using few of them, and found 2 new one and helpful (Clean Options and Debug Queries) but m not getting the results as defined in Debug Queries. only seconds and queries are desplayed. could you help it?
Please be advised that adding define('WP_ALLOW_REPAIR', TRUE); to your wp-config IS A BAD IDEA!
It allows ANYONE (even bots) to run the script /wp-admin/maint/repair.php on your site!
People (or bots) with bad intentions can excessively execute this script, causing a unwanted load on your server.
/wp-admin/maint/repair.php is meant as a resort to repair your database if your database it is messed up in a way that you can not logon to your site anymore, that's why you can enable this option in wp-config.php to fix things. It is NOT meant to be a permanently 'true' setting. Please check the wordpress codex site if you want to know the in's and out's of options in wp-config.
Thanks for the tips. Optimizing the database is so technical, but this is easy to understand.
Excellent tips. I am installing the Debug Queries plugin now!
Excellent Tips! I would install Debug Queries Plugin. However, since Database optimization and overhead removal is essential I recommend using WP Sanitize plugin http://wordpress.org/extend/plugins/wp-sanitize