wp_mysql_optimizations.sql

/* Delete revisions */
DELETE FROM wp_posts WHERE post_type = "revision";
/* Only use this if you no longer care about any of your current revisions! */

/* Delete trashed posts */
DELETE FROM wp_posts WHERE post_type = "trash";

/* Delete Unapproved comments */
DELETE FROM wp_comments WHERE comment_approved = "0";
/* Only use this if you no longer care about the current queue of unapproved comments! */

/* Delete spam comments */
DELETE FROM wp_comments WHERE comment_approved = "spam";

/* Delete trashed comments */
DELETE FROM wp_comments WHERE comment_approved = "trash";

/* Delete orphaned postmeta */
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

/* Delete orphaned commentmeta */
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

/* Delete transients */
DELETE FROM wp_options WHERE option_name LIKE ('_transient_%');
DELETE FROM wp_options WHERE option_name LIKE ('_site_transient_%');

/* Delete orphaned relationships */
DELETE tr FROM wp_term_relationships tr INNER JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) WHERE tt.taxonomy != 'link_category' AND tr.object_id NOT IN (SELECT ID FROM wp_posts);

/* NOTES:

0. Use appropriate database table prefix, i.e. replace 'wp_' with the actual database table prefix in use (the value for $table_prefix found in wp-config.php).

1. Delete Auto-drafts:

		DELETE FROM wp_posts WHERE post_status = 'auto-draft';

	You don't need that. Why? 'cause auto-drafts are automatically deleted after 7 days of going unused. They're self-cleaning, basically. No need to worry about them. They also go away when the first auto-save occurs while writing a new post.

	- http://lists.automattic.com/pipermail/wp-hackers/2012-February/042401.html
	- http://lists.automattic.com/pipermail/wp-hackers/2012-February/042402.html

2. It's simple to repair and optimize database tables using phpMyAdmin, but here's an SQL query you could use to optimize database tables:

		OPTIMIZE TABLE `wp_commentmeta`, `wp_comments`, `wp_links`, `wp_options`, `wp_postmeta`, `wp_posts`, `wp_terms`, `wp_term_relationships`, `wp_term_taxonomy`, `wp_usermeta`, `wp_users`

*/