Top Hosting Forum

WebHosting Reviews Forum


Budget DirectAdmin hosting and domain names

Wordpress blog mysql database cleanup queries

Wordpress blogging script Discussion. Wordpress Questions, Troubleshooting, Errors, plugins, templates and so on..
Hosting Forum Description
Wordpress blogging script Discussion. Wordpress Questions, Troubleshooting, Errors, plugins, templates and so on..

Wordpress blog mysql database cleanup queries

Postby hostingforum » Thu Feb 07, 2013 12:20 pm

HI, i have gathered list of usefull queries i used on my wordpress blogs with advertising articles. These queries heped to reduce mysql usage by 80% on some wordpress blogs. I do not recommend running these mysql queries before doing full backup of your wordpress blogs mysql database.

What these queries should do?
Delete post revisions, delete approved spam comments, delete all tags, delete users without any posts, optimise mentioned tables



1. Login to PHPMyAdmin which is mysql control panel of your hosting account
2. select mysql database to work with by clicking its name
3. Click Export tab
4. Export all your mysql tables into a file and save it on your computer
5. If you did not understood previous steps or not sure if you backed up your mysql properly, please dont continue with next steps
6. After backup, click tab named "SQL"
7. Into SQL query window paste this:

Code: Select all
DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';
DELETE FROM wp_posts WHERE post_type = "revision";
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE COUNT = 0 );
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE COUNT < 5 );
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);
DELETE FROM wp_usermeta WHERE user_id NOT IN (SELECT DISTINCT post_author FROM wp_posts);
DELETE FROM wp_users WHERE ID NOT IN (SELECT DISTINCT post_author FROM wp_posts);
OPTIMIZE TABLE `wp_users` , `wp_usermeta` , `wp_comments` , `wp_posts` , `wp_terms` , `wp_term_taxonomy` , `wp_term_relationships`;


You can remove some lines. Here is the description of above lines:
0) delete all approved comments which are marked as spam
a) delete all posts revisions, these are various versions of automatically saved post when user writen the post, they take the most space and in most cases wont be needed again
b) delete tags without any occurences from table
c) delete tags with less occurences than 5 from table (SIDE EFFECT: ALSO CATEGORIES ARE DELETED IF THEY CONTAIN LESS THAN 5 POSTS)
d) delete relations with tags from other table
e) delete another tags relations from other table
f) delete metadata of users who posted zero posts
g) delete users who did not made any posts
f) optimises all tables which was used in queries above and definatelly deletes all unused crap from table

After runing the queries, make sure your site and everything works perfectly, even if it looks perfectly, keep your mysql backup you did before runing queries for future.



Ad: There You can have your forum site-wide banner/text ad for $99 yearly. Contact admin


User avatar
hostingforum
Site Admin
 
Posts: 656
Joined: Fri May 20, 2011 7:58 pm
Location: Czech Republic

Return to Wordpress

Who is online

Users browsing this forum: No registered users and 1 guest

.

cron