Database cull to remove over a million rows of sludge

andy
I am the creator of CommentLuv Premium
andy
andy
You can get your own content published on this site as long as you have CommentLuv installed on your site.

Doing so means you get exposure to thousands and thousands of other CommentLuv users and your posts get sent out to the massive subscriber list.

Google loves this site and indexes it multiple times per day and posts always get lots of comments so you can be sure of some excellent exposure.

See the Write For Us page for more details

btw.. you can get this author box here

traves_sludge_001
I have spent the past two days firefighting on the server…

here’s what I got up to

here’s the sql notes I made to remove the comments and associated comment meta and then remove all users except administrators and contributors

* find all non contributors and admins
SELECT distinct user_id FROM `wp_usermeta` WHERE meta_key = 'wp_capabilities' and meta_value like '%administrator%' or meta_value like '%contributor%'

* delete all users who are not contributors and admins
delete from wp_users where ID not in(SELECT distinct user_id FROM `wp_usermeta` WHERE meta_key = 'wp_capabilities' and meta_value like '%administrator%' or meta_value like '%contributor%')

* find all usermetas that have a user_id that isn't in the user table
select * from wp_usermeta where user_id

not in (select ID from wp_users where 1)

* delete all usermeta where there are no users
delete from wp_usermeta where user_id not in (select ID from wp_users where 1)

* find all comments that are older than 1 year
select * from wp_comments where `comment_date` < (now() - interval 1 year) order by comment_date asc

* delete all comments that are not younger than 1 year
delete from wp_comments where `comment_date` < (now() - interval 1 year)

* find all commentmeta that have comment_ids that are not in the comment table
select * from wp_commentmeta where comment_id not in(select comment_ID from wp_comments where 1)

* delete all commentmeta where there is no comment
delete from wp_commentmeta where comment_id not in(select comment_ID from wp_comments where 1)

and my plugin page loads even faster than in the video due to using New Relic to drill down to what processes where causing a delay (it was a plugin call to a domain that didn't exist)

and now I can continue on with the server for the next few years without having to pay through the nose for upgrading my RAM 

Comments

  1. Andy, I bet your system running much better and more efficiently now. One of my sites has a database program and even though I know Dreamweaver very good I do not know databases. That is very frustrating because it is hard to get someone else to clean up your site without causing more harm than good.
    Robert Fowler recently posted…Social Security Benefits and Working After RetirementMy Profile

  2. Wow Andy, you must have been so relieved being able to cleanse the server. Whatever you wrote above is like “stars” or “alien to me. Is there a way to remove Subscribers whom I believed are Spammers instead of manually removing them from the database at cPanel?

    Cheers and keep that humor of yours coming ;-)
    William
    William Siong recently posted…Make Money Smartly Even If You Are A NewbieMy Profile

    • there is a plugin that removes inactive members but I couldn’t use it because I had over 80000 and it crashed the site whenever I tried so i had to do it manually.

      if you have less than a thousand or so then you should be fine with it
      andy recently posted…CommentLuv PremiumMy Profile

  3. I may be a little thick Andy, but does that mean that all comments left on the site that are over a year old have been deleted completely from the blog?
    Karen Woodham recently posted…Exclusive Interview with Marty Wilde at the Rhyl PavilionMy Profile

    • yes that’s right, they had to go, I don’t think wordpress is capable of keeping thousands of comments and millions of metas on one database only
      andy recently posted…CommentLuv PremiumMy Profile

      • I just had my cull on Blazing Minds Andy, even though my site isn’t as massive as yours, I have seen a difference in the speed and now that the rather suspect posts from the early days and more have been deleted, Google seems to be liking me again and the traffic has had an increase as well.

        Unused Tags have gone or merged with others that are so similar, the same for categories.

        Well worth spending a little time for a spring clean ;)
        Karen Woodham recently posted…We Broke Our FaceBook Record and Are Steam Rolling AheadMy Profile

        • Yes it’s amazing how useful a regular cull can be for the performance of a site Karen, so many people are scared of removing stuff that they end up suffering I other ways like loss of traffic and rankings. Just like pruning a fruit tree!
          andy recently posted…CommentLuv PremiumMy Profile

Speak Your Mind

*

CommentLuv badge