Useful queries to clean and optimize wordpress mysql database


1) Remove “wp_commentmeta” junk entries
DELETE FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments )

2) Akismet Related metadata in wp_commentmeta table
DELETE FROM wp_commentmeta WHERE meta_key LIKE “%akismet%”

3) Remove comment agent
update wp_comments set comment_agent =” ;

4) Mass Close Trackbacks and pings on all posts
UPDATE wp_posts SET ping_status = ‘closed’;

5) Delete all unused tags
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

6) Delete feed cache
DELETE FROM `wp_options` WHERE `option_name` LIKE (‘_transient%_feed_%’)

7) Delete all post revisions and their metadata
DELETE a,b,c FROM wp_posts a WHERE a.post_type = ‘revision’ LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);

9) Remove media related postmeta records which are never attached to post
DELETE FROM wp_postmeta WHERE post_id IN ( SELECT id FROM wp_posts WHERE post_type = ‘attachment’ and post_parent = 0 );

10) Remove posts which are never attached.
DELETE FROM wp_posts WHERE post_type = ‘attachment’ and post_parent = 0

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s