SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face What length to use for auto_increment fields?

    Hey guys.
    I've been coding with PHP for over an year, but I still can't figure out what kind of length would be good to use for the auto_increment fields in my mysql tables. I'm talking about the ID fields here. What are the possible disadvantages of choosing a really huge number for the length? E.g most of the time I pick something like 'MediumInt' with '20' as the length, but i haven't come across any case yet where the ids got even closer to that much.

    Sooo.. can you guys give me some fool proof method or formula I can always use when designing my tables?

  2. #2
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  3. #3
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, but I've already looked at that page many times and I still can't figure this out. Let me try to rephrase my question..

    (a) Are there any disadvantages to choosing a really huge data type / length for any field?
    (b) What numeric type should I use if a field is supposed to contain:
    (i) <= 1000 chars
    (ii) <= 10,000 chars
    (iii) <= 100,000 chars
    (iv) >= 1000,000 chars

    Can someone answer those for me? Thanks!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    (a) Are there any disadvantages to choosing a really huge data type / length for any field?

    larger table size is probably the only disadvantage

    (b) What numeric type should I use if a field is supposed to contain:
    (i) <= 1000 chars
    (ii) <= 10,000 chars
    (iii) <= 100,000 chars
    (iv) >= 1000,000 chars

    this question doesn't make sense

    the largest number possible is BIGINT, which goes up to only 18,446,744,073,709,551,615 when UNSIGNED -- but that's only 20 characters!!

    i have no idea what you could possibly be doing with a number that goes to 1000 characters!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    by the way, the number in parentheses after numeric datatypes is not the length, it merely indicates the number of zeroes to use with ZEROFILL

    so for example, TINYINT(1) can store numbers -128 to 127, and TINYINT(1000) can only store numbers -128 to 127, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, thanks for the reply!

    Well, i'm talking about auto incrementing numbers here, so whenever a new record is added its id keeps increasing, the more records in a table the larger the id.

    Let me rephrase my last question (once again!)

    Can you give me a fool proof answer to what type and length I should use if a field is supposed to contain the following number of (numeric) characters:

    (i) less than or equal to 1000 chars
    (ii) less than or equal to 10,000 chars
    (iii) less than or equal to 100,000 chars
    (iv) greater than or equal to 1000,000 chars

  7. #7
    SitePoint Wizard bronze trophy devbanana's Avatar
    Join Date
    Apr 2006
    Location
    Pennsylvania
    Posts
    1,736
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Most people use just a regular int field I believe. True it's probably never going to get to 4,000,000,000+ rows, but you don't want to have to keep changing the column type if you set it too low. Just use int
    Laudetur Iesus Christus!
    Christ's Little Flock
    Jesus is the Good Shepherd

  8. #8
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeh, thats sort of what I've been doing until now.. but at the same time, I don't want to have someone who knows his stuff about mysql look at my designed table some day, and comment on how amateur I am !

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    let me rephrase your question

    you're looking for numeric datatypes that will hold values

    (i) less than or equal to 1000
    (ii) less than or equal to 10,000
    (iii) less than or equal to 100,000
    (iv) greater than or equal to 1000,000

    not characteres, values

    the answers --

    (i) smallint
    (ii) smallint
    (iii) mediumint
    (iv) integer if over 2+ billion, bigint if larger than that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! You made it so simple. One more question though, can you also tell me what to put in for the 'Length/Values' for each of those when adding the field through Phpmyadmin?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    don't put any
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    don't put any
    if i don't put any length in, say, an int field, will it still be able to store a number such as : 100000 (100,000)?

  13. #13
    Made with a Mac! philm's Avatar
    Join Date
    Sep 2001
    Location
    Portsmouth, UK
    Posts
    735
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ^^^^^^^^
    It's not the length...
    Quote Originally Posted by r937
    by the way, the number in parentheses after numeric datatypes is not the length, it merely indicates the number of zeroes to use with ZEROFILL

  14. #14
    FBI secret agent digitman's Avatar
    Join Date
    Sep 2004
    Location
    Work
    Posts
    697
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    aiiiiiiiight....... i'm an idiot

  15. #15
    SitePoint Wizard bronze trophy devbanana's Avatar
    Join Date
    Apr 2006
    Location
    Pennsylvania
    Posts
    1,736
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By the way, int is 4,000,000,000+ (4 billion) if you use an unsigned int, which you have no reason not to for auto increment fields.
    Laudetur Iesus Christus!
    Christ's Little Flock
    Jesus is the Good Shepherd

  16. #16
    Non-Member Gator99's Avatar
    Join Date
    Sep 2004
    Location
    Florida
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have to do what is called an order of magnitude estimation then use the proper int type. Do you think the max capacity is going to be hundreds, thousands, 10s of thousands, etc. You could use overkill and use int for everything but it's not really that efficient. And like devbanana stated, use unsigned, you get twice as must capacity, an auto_increment number is never going to be negative unless superman turns the world back on it's axis so he can go back in time to stop some dastardly deed done by Lex Luthor.


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
  •