Reset/Reorder posts ID in the MySQL wp_posts table

If you want sequential numbers for your posts, then don’t use the post ID column.

AUTO_INCREMENT is meant to provide unique numbers (and split-brains in multi-master replication), it is never going to be without gaps, especially not in WordPress (revisions, autosaves, pages, contact forms, galleries and a bunch of other plugins) and especially not with InnoDB. A simple transaction failure/rollback can cause the AUTO_INCREMENT fields to increment a billion times without inserting a single row, it’s pretty normal behavior. I’ll also quote Tamas from this Stack Overflow thread:

You should never depend on the numeric features of autogenerated keys.

From a performance point of view, it doesn’t really matter. A BIGINT holding 412, a BIGINT holding 7060 and a BIGINT holding five billion are all 64-bit integers.

If you want truly sequential identifiers (for your UX or whatever) you should use your own much more predictable and isolated numbering logic.

Related Posts:

  1. Manually removing revision post types
  2. How do I find which articles are missing a featured image in The WordPress database?
  3. Remove a shortcode from all WordPress posts
  4. What do these phpMyAdmin errors mean on my WordPress databaes?
  5. What happens if I delete all the rows that represents a post revision from the posts table into WordPress database?
  6. Restore woocommerce orders
  7. How to select particular month post from table and update the post status using MySQL?
  8. Bulk find & replace on WordPress posts/pages (minus image paths)
  9. SQL trigger failes with post_content
  10. Converting Posts to Pages
  11. How to change the publishing date of each posts?
  12. Can posts have parents?
  13. Where is the old post permalink slug stored?
  14. Separate Database Tables For Different Post Types
  15. MySQL Query to Retrieve Category from wp_posts
  16. SQL Query for getting all posts in their latest revised state
  17. How are terms connected with posts in database?
  18. Moving WordPress to new server: no posts found
  19. Search & Remove Specific Shortcode From All Posts
  20. Manually delete post from database
  21. How to force update all posts after import
  22. Query All users that has post
  23. Exporting Data from WordPress into a flat table
  24. Can’t publish new posts
  25. Insert posts in two tables
  26. Instead of submiting an entire post, is there any way to submit a lite-post or a simple message?
  27. How to migrate the posts from an old custom legacy blog to a new WordPress website?
  28. Split WP_POST table based on post types
  29. Most recent post from another database
  30. What structure should post_content have in the database?
  31. How to delete all the content of a wordpress site without deleting the Post and pages?
  32. Extracting post categories
  33. How to modify post content?
  34. wp_insert_post – duration
  35. Get user categories with most posts in it
  36. Transferring WordPress Database – Serialised data
  37. Emojis replaced by ‘?’ automatically
  38. Custom order for Mysql array
  39. List authors with the last post title and order by last post date
  40. When open add new post or page WordPress add post with ID=0 continuously [closed]
  41. wp_posts table: safely remove unused columns to save database storage
  42. Anyone know why wordpress converts some html entities to their numeric equivalents?
  43. How to Mysql select a list of posts with meta_values AND all relevant categories?
  44. why there are so many posts whoes post_type is revision? will these records waste too much database space?
  45. Delete all drafts?
  46. Delete post results in “Cannot delete or update a parent row: a foreign key constraint fails”
  47. Is it possible to use WP_Query to only pull posts with attachments?
  48. Get posts and include taxonomy term
  49. Inner join overrides Advanced Custom Fields plugin’s get_field [closed]
  50. Add field to user meta table in database when link is clicked
  51. How safe is it to delete old posts edits to save database space?
  52. Delete post revisions only for a single post
  53. How to Assign / Move all Deleted post to a category
  54. Display post number by category
  55. Saving Post Data in Another Database
  56. Database hacked – random posts are modified
  57. Need to change all links in page content but not in post content
  58. How do post IDs work in WordPress?
  59. Why when I create a new post I found 2 record related to this post into the posts database table?
  60. Update field in database for users
  61. Import custom database into wp and keep the post id
  62. get last post’s link with SQL query
  63. Moved to a new server, backup was a day old and missing 3 posts, anyway to recover them from old host?
  64. query for filtering published posts?
  65. Help on conditional statement to accompany wp_insert_post function please?
  66. Is it possible to modify posts table to only read for more recent ones?
  67. WP query taxonomy optimization
  68. Menu Items disappearing/being empty on save or post edit
  69. Query to fetch custom taxonomy along with post title
  70. How to setup default value of post_id
  71. Mysql update menu_order problem
  72. Prevent a post with a specific ID from being created
  73. Reusing old post IDs
  74. Trigger on DB make problem on wordpress?
  75. WordPress MySQL Search and replace with wildcard?
  76. Comments are in database, don’t show up in WordPress backend or frontend
  77. Is there a way to save different data when USER interacts with the same POST?
  78. Add postmeta to all blog posts
  79. ‘posts’ table should not store the absolute image path
  80. Dev and prod on the same database- dev version active for IOS
  81. load post data into mysql
  82. More Than 50K Categories and WordPress Admin Panel Stop Showing Categories and Posts
  83. Clean up customize_changeset in DB
  84. Maximum number of posts per page before affecting performance?
  85. get backup file from wordpress database in x days
  86. I moved my site to another server, wp admin works so does the front page, but posts don’t work
  87. Can’t seem to replicate permalink structure for localhost development site
  88. When I click edit on a post, all the content disappear. Does anyone know how to fix this?
  89. SQL query to remove first image in all posts
  90. Given two custom post types: Automatically add meta fields from one custom post type to another
  91. Delete junk text from all the post using my sql command
  92. Load posts from external source if not found in database
  93. How to create a post based on data in a table?
  94. Is there a maximum amount of pages for a multi-page post?
  95. If meta_key exists then don’t select this post?
  96. How to make category for word post_content
  97. What effect can a large wp_post table have on overall site performance?
  98. How to update wp_posts with just the returned comment count from wp_comments (SQL GROUP BY)
  99. How can I merge a several related posts into one post?
  100. Restore posts, but only posts that exist in my database backup