Optimize a WordPress Database

Make sure you have a backup before ever doing anything with your database (In phpMyAdmin, use the “Export” tab and export whole database sql).

## 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'

## 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.
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, if 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.mysite.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@testemail.com';

## Delete All Unapproved Comments

On very popular blogs, a lot of comments can build up in the “Pending” section.
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';