Database cull to remove over a million rows of sludge


Post Views for Apr :
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