Optimize WordPress Database

Have you looked near your MySQL database since you first set it up? If you’re like most of us, you probably don’t go in there too often.

MySQL is complicated and unfriendly to use, and well, your site works as it is. So why bother?

If you do happen to venture in there occasionally though, you might find there are a few simple tricks you can pull to make the most of your database usage. From headache-saving tricks for you, to cranking a little more performance out of your server, check out the 7 tips below.

Before we start though, it will be invaluable to have a tool like phpMyAdmin to make this all easier (Ask your webhost about it, 99% of them already have it installed and available to you).

On top of that, make sure you have a backup before ever doing anything with your database (In phpMyAdmin, use the “Export” tab).

MySQL “Optimize” is a Free Speed Boost

MySQL has a built-in optimize function to clean up wasted space in your tables. It’s particularly effective when you’ve deleted a lot of data (With blogs, that mostly means comments).

The best part though is that it’s simple to do. In phpMyAdmin, just select the table, go to the “Operations” tab, and then click “Optimize Table.”

Or if you’d rather run it manually, the syntax is:

OPTIMIZE TABLE 'wp_comments'

Update (4th May 2010): Chris Messina kindly shared his results of this trick in the comments, a pretty impressive improvement!

Backups that You can Set and Forget

Backing up your database is non-optional. All computers crash at some point, and you don’t want to risk losing all of your blogging history just because you didn’t bother to set up some backups.

To see how you can make this automatic and never have to worry about them again, read our perfect hands-free database backup tutorial.

Delete All Post Revisions

Post revisions (Where WordPress automatically keeps a history of all changes to your posts) are considered a nuisance by many people, largely because of the space it wastes in your database.

For me, it makes sense to keep them enabled because we have multiple authors here. If I were just blogging on my own though, I’d disable them.

To do that, you just have to add this line to your wp-config.php file.

define('WP_POST_REVISIONS', false);

But that won’t help with revisions already stored in the database. To wipe them, run the following command:

DELETE FROM wp_posts WHERE post_type = 'revision';

Multiple WordPress in one Database

If you have the option, you should put each of your sites into its own database (And give each database its own user who only has access to that database). That way if anything happens to one, the rest of your sites will be unaffected.

Sadly though, that’s not an option on all shared hosts. On my old host, MediaTemple, you were limited to just one database. In that case, you’ll need to do what I did and change the prefix for your tables (i.e. the wp_ in wp_options etc.)

To do this, look in your wp-config.php file for this line, and change the prefix to something new:

$table_prefix = 'wp_';

Delete All Comments From a URL

Sometimes spammers do break through. It’s a shame, but so long as you catch on, you can delete it. You can do this all manually via WordPress of course, but sometimes it’s easier to run a simple MySQL command.

To delete all comments from a specific URL, you’d run this command:

DELETE FROM wp_comments WHERE comment_author_url = 'http://www.site.com';

Alternatively, if you wanted to delete all of the comments by email address, you could use:

DELETE FROM wp_comments WHERE comment_author_email = 'spammer@test.com';

 

Bonus: Delete All Unapproved Comments

On very popular blogs, a lot of comments can build up in the “Pending” section (I’ve worked with clients who have hundreds of comments that have been there for months).

In those cases, you’re unlikely to work through them one at a time (And even if you do, they’ve been there so long that no-one is going to notice).

Ideally though, you never want to get into this position. When comments need to be approved here on Pro Blog Design, I make sure to approve them right away so the little counter goes straight back to 0.

If your counter is at a few hundred though, you won’t notice when 2 or 3 more are added to it.

In that case, the best thing is just to wipe the slate clean and start again. In that case, you can delete all of your unapproved comments with this simple MySQL statement:

DELETE FROM wp_comments WHERE comment_approved = '0';

Plugins Leave Data Behind

When you deactivate a plugin, it just stops the plugin from functioning anymore. It doesn’t remove any of the data already in your database.

Some plugin authors have been diligent and now include “Uninstall” buttons with their plugins that you can run before deactivating. The uninstalls will clean things out properly.

If your plugin didn’t come with an uninstall though (Most don’t), you’ll have to clean it out yourself.

The easiest place to start is plugins that create whole tables. When I used to run the SEO Title Tag and Search Meter plugins here, they both created their own tables in my database to store their settings.

Deleting them is easy, just click “Drop” in phpMyAdmin after selecting the table (please do make sure you’ve selected the table though. If you aren’t on the right page, you’ll “drop” the whole database!).

Other plugins don’t use as much data though and just store their settings in the wp_options table. The easiest way to find these is just to browse the table and delete the rows you don’t need (Again, a tool like phpMyAdmin makes this simple).

Deleting individual options like this may not be worth the effort though. It might save you a few kilobytes, but that’s all. I’ve never done it personally, but if you want to be ultra clean, it won’t take much effort to do.

Copying Your Widget Data

All of your widget data is stored in your database, including which widgets are being used in which sidebars, and the settings for them.

If you want to copy these over to a new site without manually doing it all via WordPress, then run a search in your wp_options table for %widget% (in the “Option Name” field).

The results you get will contain all of your settings for each widget.

Conclusion

Those are the 7 most useful tips that I use when managing WordPress installations. Are there any others that you use as well?

If you have a database tip to share, I’d love to hear it in the comments!

Enjoy this post? You should follow me on Twitter!