Default Value For Int

[Still Learning]

Hi, when I read the mysql manual it lists ranges of :

INT -2147483648 to 2147483647 or 0 to 4294967295

which when I count comes to 10 digits. However, when i create a new table in sqlyog and define a PK as INT without setting a value for length, it seems to allot a default value length of (11).

While this may not be a big prob or issue, what is the “best practice length” for setting PKeys? Obviously for a table like NAME_PREFIXES (Mr. Mrs. etc) a SMALLINT or even TINYINT would be best, but what about for say, MEMBERS ???

Thanks in advance.

[peterlaursen]

1) one byte is required for the ” – ” sign

2) I practically always make an autoincrement PK a BIGINT UNSIGNED .. but probably 'overkilll' in most situations!

[Still Learning]

1) one byte is required for the ” – ” sign

that makes sense, so if I set INT unsigned it will default to 11 and if not then default to length 10

2) I practically always make an autoincrement PK a BIGINT UNSIGNED .. but probably 'overkilll' in most situations!

correct me if I am wrong, but it is possible to change (increase specifically) the size later on (as data needs require) without much difficulty right? Also, in theory, better performance is obtained from a smaller number, all other things being equal?

[peterlaursen]

You can always change from a tinyint to a bigint for instance with ALTER TABLE!

.. but what does 7 bytes of disk storage cost today? 🙂

[Still Learning]

.. but what does 7 bytes of disk storage cost today? 🙂

>>>>> obviously you are right, thanks though for pointing it out as this is the sort of thing us newbies wouldn't realize until later given the preponderance of advice in sql books to minimize size etc. — any other top of the mind “real-world” best practices you can suggest??

[Author]

Posts