Re-sequencing IDs in the database

  • Resolved kvandekrol

    (@kvandekrol)


    15 years ago

    I use the ID in my URL, so it’s important to me that the IDs be at least somewhat sequential. When moving from Blogger to WordPress, I imported about 50 posts and the ID for the last one was in the 50’s. However, since doing that a couple of months ago, I’ve only made maybe four more posts, yet my IDs have shot up to around 115.

    I did some research and it appears that every revision creates a new entry in the database. I disabled revisions in WordPress based on a post I found, so this should stop happening from this point forward, but I would now like to go into the database and 1) delete all of the revisions, and 2) resequence the IDs so that my post numbers are in the 60’s rather than in the 110’s. Essentially I want to fill in all of the gaps.

    Is there a SQL script I can use to accomplish this quickly?

    I don’t have anyone else linking to specific posts right now since my WordPress site is brand new, so I am not concerned about the permalink URLs changing. I just want to make sure to get this done ASAP because it will be much harder to change a few months from now. I also want to make sure this won’t break anything that’s already there. (I have no comments yet on the WordPress posts, and I don’t use metadata, so wp_comments and wp_meta aren’t of much concern to me – I will only be correcting the IDs of posts made on WordPress, not the ones imported from Blogger.)

Viewing 5 replies - 1 through 5 (of 5 total)
  • Samuel B

    (@samboll)

    15 years ago

    download wp-backup plugin
    http://www.ilfilosofo.com/blog/wp-db-backup/
    after activating go to
    tools – backup
    do a back up and check “Exclude post revisions”
    import this backup to db – this will eliminate the revisions

    Thread Starter kvandekrol

    (@kvandekrol)

    15 years ago

    Will it renumber all of the post IDs as well to fill in the gaps?

    t31os

    (@t31os)

    15 years ago

    You’d need a custom script to re-write all the IDs…

    Removing the revision posts is just a simple SQL query…

    DELETE FROM wp_posts WHERE wp_posts.post_type = revision

    Be sure to backup before running anything like the above..

    Attachments in posts are also given an ID, so these also count toward the post ID, aswell as pages..

    I’m not sure what you’re modding to get the revisions removed, but i doubt this will effect pages or attachments which both also add to the count aswell.

    Thread Starter kvandekrol

    (@kvandekrol)

    15 years ago

    I am cool with pages and attachments adding to the count. Skipping a number here and there is fine. I just don’t want to jump 60 pages for four posts! (I do a lot of revisions, BTW!)

    Any ideas what the custom script would look like to rewrite the IDs, or would it be best to just do this by hand?

    Thread Starter kvandekrol

    (@kvandekrol)

    15 years ago

    I decided to just renumber them by hand with an update script. There were only 8 of them that needed to be corrected so it was much quicker. Worked like a charm.

    Thanks for the help!

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Re-sequencing IDs in the database’ is closed to new replies.