mysql auto_increment holes

Anon

Is there an easy way to get auto_increment to fill in gaps, rather than just doing 1+maxValue?

I've got tables set up with auto_increment fields. But as I delete rows, auto_increment only sets a new entry to 1+maxValue. So if I delete the row with auto_increment value 2, 2 is gone for good and will never get used again.

I haven't seen any discussion about this - is auto_increment set this way for a reason? Is it bad form to try and reuse deleted auto_increment values?

Anon

Just out of curiousity, why do you need it do this ? 🙂

Anon

Well, it seems to me, if it just pulls the maxValue all the time. And is not filling in the holes as rows get deleted. Then over time you'll max out that column's spec.

I'm using the auto_increment to generate unique userID's on my system. Now, I've set it up as a mediumint(6). I realize that with 1,000,000 users it will be maxed out. But, if for some miracle, our system reaches auto_increment number 999999 - then I'd like to know that we actually have 999999 user accounts in the database. Not 500000 accounts because as we've gone along deleting accounts as they're no longer used and the auto_increment isn't taking advantage of the free'd up account numbers.

Does that make sense? Is this not something that's been discussed before with the auto_increment feature? Or is there some design/programming principal that says it shouldn't resuse deleted numbers?

Anon

mediumint(6)

The '6' just affects the display width; the actual storage available from a mediumint is (presumably) +- 8,388,607 so you've got more space than you think. But why on earth use a mediumint anyway? Surely it can't be that the space occupied by your user id's is going to be a problem if you use a "normal" int instead of a medium?

Anon

Surely it can't be that the space occupied by your user id's is going to be a problem if you use a "normal" int instead of a medium?

I totally agree. If you want a count of users when the id = 999,999, do a count(pkey) on table. There should never be a reason to care that there are holes in a primary key field as long as the database was designed correctly and the primary key doens't 'mean' anything other than a unique identifier.

If you are worried about referential integrity, use a different db.

Anon

Ay - all true. For us, the userID is the primary key. It's 9 digits. Consisting of a 3 digit school code and 6 digits unique.
I have been using an auto_increment field to make sure the 6 digit portion is really unique. Hence the mediumint(6) is all we really need.

The 9 digit total is because we're fitting the userIDs into a legacy db as well. In the space normally filled by Soc. Numbers.

But 6 digits or 1,000,000 users can get used more easily if we're not recycling numbers from accounts that have been dropped.

BTW - these userIDs are internal to the databases only. No users ever actually see their ID number.

I guess I've just been using the auto_increment as a shortcut to creating the 6 digit portion of the IDs. Given the 6 digit limit on unique ID numbers is there a better way to do this? At this point I guess if I want to recycle numbers, I'll have to do it in PHP with a couple of mysql calls.

Anon

But if the number is an "internal id" and is never seen by the user, it's a huge mistake to make it a segmented number where the components have some meaning. You should be using a simple autoincrement number; this will give you 9 digits of space.

Anon

Okay, cool. I didn't realize creating userID format to carry the school code as well as be a unique number was such an issue.

It made for some nice optimizations - since we had more information packed into a smaller space. I didn't have to set aside an extra variable in the session. And I didn't have to do extra mysql calls or joins to get the user's school to draw their interface.

I don't know if dropping the school code from the ID's will go over, though. Since school admins are maintaining two other databses that will contain user information from our server. And by hand, no less! They refused to allow us to connect up and just put the information in there directly. (Lousy backwards university).

Anyway - thanks to all of you for the discussion on this.

BTW - this may be the wrong forum - but has the workings of the auto_increment in mysql been debated before? And why is it such a bad idea to put extra meanings into unique identifiers?

Anon

BTW - this may be the wrong forum - but has the workings of the auto_increment in mysql been debated before? And why is it such a bad idea to put extra meanings into unique identifiers?

Come on man...have you learned nothing from your current problem? 🙂

The problem you're experiencing now is a perfect example of why extra meaning for unique identifiers are bad.

Another example is: if you let the user see it, they will want to change it. I've worked on a project where the project lead made the decision to use a pkey as a user visible value and it had meaning, thinking that no user would ever need to change it. Sure enough one of the first users said 'How do I change this to a number I want?'

Learn from others mistakes and fix those you find once you know them to be mistakes. It will make maintenance such a breeze.

Tom

Anon

Alright, alright. You guys are right. I'm just aghast at the amount of work involve in redoing the database design.

And dig this. Talking with management about this has revealed a whole host of other design differences. Stemming form differing ideas of what we were trying to do. As well as different definitions of terminology and such.

At this point I'm having to redesign at least 80% of database and corresponding scripting. I'm just cutting my losses and trying to be as specific as I can about issues.

Again, all thanks to you guys. Who speak truth - but it's that awful, nasty, painful truth we usually try to hide from. Doh!