Resequencing Auto Increment Tables

  • [ Moved to the Fixing WordPress sub-forum. ]

    The following tables in WordPress Multisite each have an Auto Increment value as their primary index id (## represents the blog id in multisite):

    • wp_##_commentmeta
    • wp_##_comments
    • wp_##_links
    • wp_##_options
    • wp_##_postmeta
    • wp_##_posts
    • wp_##_termmeta
    • wp_##_terms
    • wp_##_term_taxonomy
    • wp_usermeta
    • wp_users
    • wp_blog_versions
    • wp_blogs
    • wp_site
    • wp_sitemeta

    Is there any harm in periodically resequencing the AI by resetting AI to 1, sequencing the table rows from 1 to whatever, and rebuilding the next AI value accordingly?

    The reason for my inquiry is a couple of the tables are getting way out of hand with extremely high next AI values due to removal/deletion of rows in the table.

    Thank you for offering advice and opinion on this desired action.

    • This topic was modified 7 years, 10 months ago by frank tredici.
    • This topic was modified 7 years, 10 months ago by frank tredici.
    • This topic was modified 7 years, 10 months ago by frank tredici.
    • This topic was modified 7 years, 10 months ago by Jan Dembowski.
Viewing 5 replies - 1 through 5 (of 5 total)
  • Moderator Steven Stern (sterndata)

    (@sterndata)

    Volunteer Forum Moderator

    What does it matter?

    More practically, those IDs are often foreign keys in other tables, so this would be a big mess to program.

    Thread Starter frank tredici

    (@frank13)

    What does it matter?

    Just wondering…that’s all.

    My wp_##_options table looks like this:
    Rows 3,984
    Next autoindex 2,036,971

    So I was wanting to rebuild the AI in order to headoff long-term issues with potentially exceeding the upper limit of the AI

    • This reply was modified 7 years, 10 months ago by frank tredici.
    • This reply was modified 7 years, 10 months ago by frank tredici.
    Moderator Steven Stern (sterndata)

    (@sterndata)

    Volunteer Forum Moderator

    the ID for wp_posts and wp_options, for example, is a BigInt. It has a max value (unsigned) of 18,446,744,073,709,551,615.
    You’re not going to run out soon.

    Thread Starter frank tredici

    (@frank13)

    To my specific install, correct…I should not run out for a while.

    But more to the theoretical aspect of the question, because as WP developer that adds client WP Sites to my WPMSN install from time-to-time, I should never ever resequence AI values or else the WP Core and MultiSite tables will get corrupted.

    Is that the best answer to the question?

    Moderator Steven Stern (sterndata)

    (@sterndata)

    Volunteer Forum Moderator

    He’s right.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Resequencing Auto Increment Tables’ is closed to new replies.