Common database optimizations for WordPress sites.

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 Jetpack Feedback Spam */
SELECT * FROM wp_posts WHERE wp_posts.post_type = "feedback" AND wp_posts.post_status= "spam";

/* 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);