WordPress websites can easily under perform (or use too many resources – a problem with many shared hosts – or even break) if their SQL database is not optimized or if it becomes too big, which is often the case especially with autoblogs. It is recommended to run a little maintenance every couple of month (this is part of our website maintenance service). Simply login into your site and have a look (right-click on the pics below to enlarge full size in a new tab):

In this case the situation is not too bad (actually it is, but we often see MUCH worse!). As you can see, first the site is not updated, so update everything (theme and plugins) until you do not see a little number next to ‘updates’ (top left). Second, we have over 16,000 posts and almost 2,000 comments, these will make your database sluggish. Try to avoid all these comments from spammers, make sure that the settings under Settings>Discussion and Genesis>ThemeSettings (if the site uses Genesis) are as follows:

Now login into your Cpanel and click on phpMyAdmin. Select the table and click on Structure, notice the large size of over 59Mb!

If you check all the tables and click optimize (which is what many hosts recommend) you won’t achieve much, so we’re going to run a few sql commands for a more thorough cleansing. Click on SQL at the top, a blank box will open:

paste into it the following commands as per picture above:

DELETE FROM `wp_posts` WHERE `post_type` = “attachment”;
delete from `wp_options` where `option_name` like ‘_transient_timeout_rss%’;
delete from `wp_options` where `option_name` like ‘_transient_rss_%’;
DELETE FROM `wp_options` WHERE `option_name` LIKE (‘_transient%_feed_%’);
DELETE FROM wp_posts WHERE `post_type` = ‘post’ AND DATEDIFF(NOW(), `post_date`) > 30;
delete from wp_comments WHERE comment_approved =”0″;
delete from wp_comments WHERE comment_approved =”1″;
UPDATE wp_posts SET ping_status = ‘closed’;
DELETE FROM wp_postmeta WHERE wp_postmeta.meta_key != ‘NULL’;
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);
DELETE FROM `wp_options` WHERE `option_name` LIKE (‘_transient%_feed_%’);
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);

Now this is quite an EXTREME CLEANING; in particular, line 1 will remove ALL PICTURES from the media library, line 5 will delete ALL posts OLDER THAN 30 DAYS, lines 6 and 7 will remove ALL COMMENTS. So before running this, you may want to remove line 1, and perhaps edit that ’30’ in line 5 to something else (it’s the number of days so if you change this to 60, all posts older than two months will be deleted, etc. Notice that it does not delete pages. We usually put a very low number there (or remove from ‘and date…’ to the end of line), thus deleting pretty much all posts). Line 14 is likely to give a syntax error but it does not matter. Once satisfied with the settings (obviously EXPORT the database before running anything drastic such as the above) click GO. (If you go as per above and remove all posts, all pictures etc. do not panic; if the site is an autoblog, new posts with pictures will get automatically created within hours, or you can force a manual update from the syndication plugin. The commands above do NOT delete your affiliate adverts or any customizations or menus or site settings, but they might delete the graphic header, which you can always easily re-upload).

As you can see we’ve cleaned up quite a lot! In order to reclaim this space simply Check All, and from ‘With selected’, pick Optimize Table.

Optimize will only take a fraction of a second and as you can see the database is now almost 10 times smaller! Go to your WordPress Admin panel again and enjoy the results:

Only about a thousand posts, no comments, and our site is fully updated…. nice! One last thing that you may want to do, is to also make room under your hosting otherwise the site will reach its quota pretty soon, and if so, it will stop posting pictures and work properly and also, your host will get mad at you. Simply click on the File Manager from Cpanel, go to the folder(s), for example go to public_html/wp-content/uploads/2013, select and delete a few past months, in this example (it is now March 2013) we are deleting Jan and Feb 2013. (The command on line 1 above deletes all pictures from the media library but NOT from your folders). Just like the SQL commands above, this is also a quite drastic undo-able procedure, so make sure that you have understood these instructions and that you know what you’re doing.

Obviously, there are many other things that you can do to keep your site from becoming sluggish, huge and unresponsive. One other of course would be to install a cache, good topic for another post… stay tuned! 🙂