Tons of Twitter rows in my database

2 comments

1. 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;
   

   Log in to Reply

2. 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.

   Log in to Reply