Reset/Reorder posts ID in the MySQL wp_posts table

Question

I have a Wordpress site that is considerably popular. I am in the process of optimizing the site before adding other plugins such as the WPML plugin which I suspect will be using custom post types. The site already has other plugins which I don't suspect use the wp_posts table for custom post types except for 1 or 2 just to create.

Is it possible to rebuild/update/reorder the ID column in the wp_posts table such that the post IDs start from 1 and increase without any gaps.

The wp_posts table contains 3,349 rows. The site contains around 412 posts (post type) in the wordpress wp_posts table. The largest number in the ID column is 7060 (this is very large)and any new post I add will increase the number to 7061. The rest of the rows in the table are other post types such as attachments some of which were created by old plugins. There is 2,786 rows for the attachment post type which is a very large number considering I won't be using these attachments anymore. The 412 posts contain all the information needed for the site. I am converting the images to inline SVG and latex. Most of the images were numbers. So after all this I will probably have 412 rows in the wp_posts table.

I know I could reset the AUTO_INCREMENT back to 1 by using; ALTER TABLE tablename AUTO_INCREMENT = value;
But I am not sure how to reorder the post IDs. I want to see what precautions I should be aware of before attempting this. I think this is important to simplify the Post IDs because they're going to be used all over the Multi site. If there is a solution that could reorder and update the entire references to the posts across other tables that would be great!.

I am aware that this is a MySQL feature and I will have to work with AUTO_INCREMENT.

I am aware that other post types such as attachments may contain a parent id linking to the original posts. I am not so worried about attachments because I want to get rid of the 2,786 attachments anyway.

I will probably loose the tags and categories but simplifying the post IDs is vital. I will be rebuilding the tags and categories anyway. Is there a neat way to keep these tacked.

Is there any other dangers I should worry about? Because lots of scripts/features are going to be built around the main site posts I want to make the post IDs as unique as possible at this point. Having a gap of 100s is not unique for me.

I hope someone has the perfect solution for this. Am I attempting a very dangerous idea?

Answer 1

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.

Answer 2

That post ID is used to cross-link data across many different tables by the WordPress Core, plus whatever plugins might do or whatever the theme might do. There is also potential to break links if your site has ever used ?p= permalinks. So yes, this is a very dangerous idea.

To properly reset your post ID's you are going to be in for a lot of work, but that work would be wasted. WordPress uses an unsigned BIGINT for that field which is capable of an upper limit of 18,446,744,073,709,551,615. You will never get there, with or without the "gaps" between posts.

Additionally, everytime WordPress makes a database entry you will have to redo the work, as WordPress makes no effort to keep the IDs tidy, as you seem to (mistakenly) think is necessary. So everytime WordPress modifies the database, you are going to have re-clean it. That is a lot of code to write to put the system in place, and a lot of CPU time to run it.

Answer 3

If the only thing you care about are the posts, then you can export the posts and import them to a new database.

I am not sure if the built-in exporter stores the post IDs and attachments therefore you might end up needing to write your own, which if you care only about post content should not be too hard.

The problem is that you should not really use an internal software ID as an external identifier, it is just bad for your health. If you need to have a unique easy to remember identifier, just create you own meta field and use it for that.