Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have 264,307 "http" (post_type = http) rows in my wp_posts table. 99% of those have them have this post_title:

GET http://twitter.com/statuses/user_timeline/@xcentric.json?count=100

and "error" as the post_status.

Can I simply delete these without it affecting my site? My wp_posts table is 5gb!

Thanks

share|improve this question
1  
I'd be more worried about why are they there in the first place... –  Brady Jan 5 '12 at 15:39
    
Agreed. Pretty sure they came from a plugin which I've disabled. –  Chet Loesing Jan 5 '12 at 22:07
add comment

2 Answers

up vote 1 down vote accepted

Here is a bait-and-switch approach to deleting all those rows:

CREATE TABLE wp_posts_new LIKE wp_posts;
ALTER TABLE wp_posts_new DISABLE KEYS;
INSERT INTO wp_posts_new SELECT * FROM wp_posts
WHERE post_type'http'
AND post_title<>'GET http://twitter.com/statuses/user_timeline/@xcentric.json?count=100';
ALTER TABLE wp_posts_new ENABLE KEYS;
ALTER TABLE wp_posts RENAME wp_posts_old;
ALTER TABLE wp_posts_new RENAME wp_posts;

Now start using you website.

When you are absolutely sure wp_posts is working fine, you can then delete old file:

DROP TABLE wp_posts_old;

or you can keep it around for a few days and drop it later.

If you want the old table put back right way, switch it back in like this:

ALTER TABLE wp_posts RENAME wp_posts_new;
ALTER TABLE wp_posts_old RENAME wp_posts;

Give it a Try !!!

UPDATE 2012-01-05 17:27 EDT

To DELETE http posts that have twitter in the post_title

CREATE TABLE wp_posts_new LIKE wp_posts;
ALTER TABLE wp_posts_new DISABLE KEYS;
INSERT INTO wp_posts_new SELECT * FROM wp_posts
WHERE NOT (post_type='http' AND LOCATE('twitter',post_title) > 0);
ALTER TABLE wp_posts_new ENABLE KEYS;
ALTER TABLE wp_posts RENAME wp_posts_old;
ALTER TABLE wp_posts_new RENAME wp_posts;
share|improve this answer
    
Nice! Thanks Rolando. –  Chet Loesing Jan 5 '12 at 21:56
    
Let me ask you this: how would I grab any post_type 'http' that had "twitter" in the post_title (instead of the full URL)? Thanks again! –  Chet Loesing Jan 5 '12 at 22:12
    
Looks like the updated code is keeping the post_type='http' rows with "twitter" and deleting the others... is it possible to reverse that? Thanks again! –  Chet Loesing Jan 6 '12 at 22:17
    
I updated it by adding NOT around the WHERE clause –  RolandoMySQLDBA Jan 6 '12 at 22:30
    
Thanks Rolando, that works! I think I also got it to work by using "Not Like": CREATE TABLE wp_posts_new LIKE wp_posts; ALTER TABLE wp_posts_new DISABLE KEYS; INSERT INTO wp_posts_new SELECT * FROM wp_posts WHERE post_type='http' AND post_title NOT LIKE '%twitter%'; ALTER TABLE wp_posts_new ENABLE KEYS; ALTER TABLE wp_posts RENAME wp_posts_old; ALTER TABLE wp_posts_new RENAME wp_posts; –  Chet Loesing Jan 6 '12 at 22:45
show 1 more comment

Is there any post_content for those rows? Presumably they were made by some Twitter plugin, so see if there's one installed and if it's set to active or inactive. If the data isn't being shown or used anymore on the site / blog, you should be able to just remove those rows since they no longer serve a purpose.

share|improve this answer
    
Oh, and since it's a Twitter time-line, the data will still be on their server so it won't just be gone if you remove it from your WordPress database. –  stealthyninja Jan 5 '12 at 16:12
    
It does have some post_content but it looks like code: [cl.ly/1N1I140e021c3s3Q421H] I feel pretty confident they came from a plugin which I disabled yesterday. Since disabled there haven't been any more posts. Thanks stealthy. –  Chet Loesing Jan 5 '12 at 22:02
    
Is is OK to mention which plugin you think was doing this? –  davemac Jan 11 '12 at 0:18
1  
Twitter Widget Pro but I'm on an old version of both the plugin and WordPress (2.9.2) so that is probably more to blame. Working on upgrading now. –  Chet Loesing Jan 17 '12 at 23:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.