1

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?

  • 2
    I've seen plenty of sites with a lot more than 7000 posts, I very much doubt you will get the performance gains you expect from doing this, and keep in mind you will need to update all the post meta tables, the term relationships, any options that store post IDs, clear all your object caches, transients, and any URLs that contain post IDs will no longer work or lead to the wrong location. Your post IDs are as unique as they ever will be, a gap doesn't change that ( and gaps will happen once a post is deleted anyway ). I suspect micro-optimization – Tom J Nowell Nov 20 '14 at 15:29
  • The latest ID is too large (7060) which could be reduced to 412. I have a feature across the entire site where my subscribers will be constantly using the post ids to attach posts as articles to "documents" and other items within the site using an app. This will happen constantly so having smaller numbers will make it easier to remember. Can't I force wordpress to use unused IDs? I am not only trying to optimize for performance but to make the table neater before I move forward. – user3072613 Nov 20 '14 at 21:58
  • 3
    Those are not small numbers, 2 factor auth numbers used by Google and Banks are 6 digits, and science says we can hold between 5 and 7 digits in our head fine. What you actually have is a usability issue, making your users remember numbers is an awful, awful idea, and will not scale. What happens when you have 9,999 posts? Eitherway what you're trying to do requires a lot of MySQL knowledge, you'd be better off asking how to implement a proper user interface with a dropdown or an autocomplete to pick a post rather than going with the copy paste a number scheme – Tom J Nowell Nov 21 '14 at 0:53
  • @TomJNowell is absolutely correct. The system you've implemented is awful. Additionally, even if you were to make these changes you'd have to keep making them over and over on a weekly, even daily, basis. That is ridiculous. I'd argue that you are trying to make the database work in a very un-database-like way. This isn't even a WordPress issue so much as a data data storage/data integrity issue. You are driving a car off the bridge and into the water, then asking us how to make it swim. – s_ha_dum Nov 27 '15 at 15:03
3

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.

0

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.

  • I am trying to understand how much this work is. I am thinking it is not that much. I am not interested in Wordpress attachments feature anymore (SVG is better). So I could just delete all attachments (reduce the rows by over a half). I am about to rebuild the tags and category with a better hierarchy so the categories and tags are not that important. Most of the plugins are popular plugins like: WP to Twitter, WP Statistics, WP User Frontend Pro, WordPress SEO, W3 Total Cache. Surely I could write a script that could rebuild the ids starting from 1 while updating every the id has been used – user3072613 Nov 20 '14 at 22:17
  • ...in every table in the database. How would I force MySQL to reset the IDs to start from 1 and increase to 412? – user3072613 Nov 20 '14 at 22:23
  • 3
    It is a lot of work that will need repeating anytime a post, revision, or media file is deleted, with no guarantee it will be foolproof – Tom J Nowell Nov 21 '14 at 0:55
0

If the only thing you care about are the posts, then you can export the posts and import them to a new DB. I am not sure if the built-in exporter stores the post IDs and attachments therefor 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.