Database cull to remove over a million rows of sludge

Bio
Twitter
Latest Posts
Write For Us

Latest posts by andy (see all)

Go Burrito Lancaster Reviews - March 12, 2015
BOL Newsletter #57 – Contest , Sales dissapointment and New plugin coded - July 7, 2014
BOL Newsletter #56 – launch finished and new direction in 4k - June 27, 2014

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

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