What length to use for auto_increment fields?

[digitman]

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?

[stereofrog]

http://dev.mysql.com/doc/refman/4.1/...ric-types.html

[digitman]

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!

[r937]

(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!!!

[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

so for example, TINYINT(1) can store numbers -128 to 127, and TINYINT(1000) can only store numbers -128 to 127, too

[digitman]

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

[devbanana]

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

[digitman]

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 !

[r937]

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

[digitman]

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?

[r937]

don't put any

[digitman]

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)?

[philm]

^^^^^^^^
It's not the length...

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

[digitman]

aiiiiiiiight....... i'm an idiot

[devbanana]

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.

[Gator99]

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.