Optimizing your database is a necessary part of WordPress housekeeping. Things can start to pile up in there… post revisions, old spam comments, etc., and your blog can start to get sluggish. We’ve gathered a number of solid tips for getting your database into shape without having to be an expert.

Warning: Back up your database in case you screw up. Yes, people still need to be reminded to do this. I know you’ve heard the horror stories. ;)

Delete Post Revisions and their Meta Data

This is probably one of the biggest things that will clutter up your database, especially if you’ve had a blog running for a number of years. Clean them all out with this simple query:

1

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

Source: http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/

Delete All Pingbacks

Yes, even pingbacks get stored in your database. Clean them out in one quick query:

1

DELETE FROM wp_comments WHERE comment_type = 'pingback';

Source: http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/

Delete All Spam Comments

If you’ve gotten a lot of spam comments, you don’t want those piling up in your database. Here’s a way to delete them all:

1

DELETE from wp_comments WHERE comment_approved = '0'LETE from wp_1_comments WHERE comment_approved = '0'

Clean Out Unused Tags

This query will identify and remove tags that have been created but are unused in your WordPress blog:

1

SELECT * From wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;

Source: http://www.catswhocode.com/blog/wordpress-10-life-saving-sql-queries

Disable All of Your Plugins

This query will help you to quickly disable all of your plugins so that you can do maintenance, determine the source of plugin conflicts, or upgrade your installation. This can be especially helpful if you have a lot of plugins in operation on your site.

1

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';}

Source : http://www.wprecipes.com/how-to-disable-all-your-plugins-in-a-second

Remove Orphaned Options

WordPress uses the options table to store plugin settings and many internal settings. The Clean Options plugin will determine which options are orphaned – options that do not have any files that “get” their values. You will be able to view them and decide which ones you want to remove from the options table.

Determine Where Your Blog is Slowing Down

If your blog has been slowing down and you don’t know why, download the WP Tuner plugin and find what is causing it. This plugin operates as a performance analysis toolkit and you can customize it with hooks and specific requests for loading time information.

Don’t Get Your Hands Dirty – Let the Plugin Do It!

If using phpmyadmin scares you, then try WP-Optimize which will optimize your database in one click. It allows you to remove post revisions, comments in the spam queue, un-approved comments. It will also display database table statistics that show how much space can be optimzied and how much space has been cleared.

Give your WordPress site a little health check-up today and apply these tips to get it running faster. This post by no means covers all of the tools available to you, so if you’ve had a good experience with any other database optimization techniques, please share in the comments. Happy housekeeping!