Advanced WordPress Operations

SQL Statements to Clean Up WordPress Database

The WordPress Database can get big pretty quickly, but here are some SQL Statements that can help you in cleaning it up. It is not uncommon to go from 400MB to 20MB in Database Size, which will in turn speed up your WordPress Site.

1. Delete all unapproved Comments

DELETE FROM `wp_comments` WHERE `comment_approved` = 0

2. Delete complete Akismet Comment History

DELETE FROM `wp_commentmeta` WHERE `meta_key` LIKE '%akismet_%'

3. Delete all Post Revisions

DELETE FROM `wp_posts` WHERE `post_type` = "revision";

4. Close all Post Trackbacks

UPDATE `wp_posts` SET `ping_status` = 'closed';

5. Delete all Users with no Posts

DELETE FROM `wp_users` WHERE ID NOT IN (SELECT DISTINCT `post_author` FROM `wp_posts` )

6. Update admin password

UPDATE `wp_users` SET `user_pass` = MD5( 'new_password' ) WHERE `wp_users`.`user_login` = "admin_username";

Find all post types

Some plugins create custom post types, and leave them in the database even after deletion. To find out all the used post types you can execute:

SELECT DISTINCT(`post_type`) FROM `wp_posts` WHERE 1

To delete a certain post_type you can use:

DELETE FROM `wp_posts` WHERE `post_type` = "<post_type>";