View Full Version : Can I start AUTO_INCREMENT back to fill in unused numbers?

02-16-2007, 06:57 PM
I've got an InnoDB table of people who are identified by a PersonID (primary key), and it's passing 50,000 and growing quickly. My users have deleted thousands of records in the past, so there are thousands of unused PersonID number's that can be used that are less than 50,000, where it is now. Can I set the AUTO_INCREMENT back to, say, 1 and have it fill in the empty numbers? Is that even possible? Will it just look for the next available, unique number and use it?

02-16-2007, 08:21 PM
I think this might be possible, but not a good idea.

you can make the counter start at whatever number you want,
but don't any of these records link to over records?
also, it will come up with an error if it trys to use a number thats already in use.

and, 50,000 is nothing for mysql. just using an "Int field" type you can use up to 11 digits.

02-16-2007, 08:24 PM
your application should not rely on there being no gaps in the sequence of numbers. your users should not know anything about the auto increment column, nor should you display it to them. The only reason you should use an auto increment column is when there are no other obvious candidate columns for your primary key. If you have a table using Social Insurance Numbers for instance, those should be your primary key. when all other columns in the table may have duplicates, then and only then should you consider creating a column as your primary key. At that point, the column is only so you don't have duplicate rows, and then you show the users the rest of your columns and not the primary key.

02-16-2007, 08:25 PM
note also that 50,000 is an insignificant number, check out the values that can be held by INT or BIGINT. mysql can handle tables with millions of rows without problem.

02-16-2007, 08:30 PM
Sorry, I wasn't saying that 50,000 was getting too big...ok, I'm gonna let out a previously unknown fact...I have OCD...ok, not really. lol But I like things to be organized, and if there's a way to not have a ton of gaps, I'd like to be the most efficient as possible. That's the only reason I asked. :D

02-16-2007, 08:34 PM
even with gaps it's still just as efficient as it would be with none.

02-16-2007, 08:53 PM
You can reset the auto-increment number, but it's not going to automatically skip a number that is already being used, so it's a very bad idea to do this.

I don't usually start my auto-increments at 1... I like to do 100000 or 900000, so I can identify the ID as an ID when debugging and looking at the data. I guess my point would be, why is it important to you that there are unused numbers?

edit: whoops I started this reply a hour ago and didn't see any of the other replies and I didn't say anything new, really...