Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts

    Can I start AUTO_INCREMENT back to fill in unused numbers?

    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?

  • #2
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    hmm,
    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.

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #5
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    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.

  • #6
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    even with gaps it's still just as efficient as it would be with none.

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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...
    Last edited by Fumigator; 02-16-2007 at 08:06 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •