Tons of Twitter rows in my database

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;

UPDATE 2012-01-05 17:27 EDT

Related Posts: