SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    How come myPHPadmin doesn't make use of deleted row numbers?

    I just did a practice insert that put in 1700 rows. When I verified that it worked, I deleted all of them. The next rID that was used, on the next insert, was still 1700 higher than I thought it should be. Won't mySQL make use of primary row numbers that have been deleted? Otherwise I can see that primary key field getting quite high over time.

    Thank you.
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    No the auto_increment just keeps on counting over time. Indeed, that value can get quite high over time but the maximum value of an int is ~2 billion (~4 billion if you make it unsigned) so that should most likely suffice.

    If you're wondering why id's don't get "recycled", consider a scenario with two tables, let's say student and grades, are linked via id's. Let's now say you remove a student but not the grates that belong to the student [from the grades table].

    If a new student that enrolls were to get the recycled id from the student that was just deleted it would automatically get the grades of that student, and that's not what you'd want to happen
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    Otherwise I can see that primary key field getting quite high over time.
    let's say you use INTEGER UNSIGNED and insert 10 rows per second

    any idea how long it will be before you run out of numbers?

    spoiler: more than 10 years

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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