Hi everyone
I started optimising my column types in our small database.
Previously all ID columns were INT(10) but I started changing these depending on the length of the values.
For example I changed a few to SMALLINT because the values they'll be storing are less than 65000.
I also changed them all to UNSIGNED when I don't need negative values.
I also changed a few VARCHARS to CHARS of specific length, for columns like currency/country codes which are wither 3/2 chars or NULL.
But here's my question... Are there any differences between an INT(5) and a SMALLINT(5) in terms of performance/data stored?
Or are the INT and *INT data types basically the same but have different default lengths when no length is specified manually?
Cheers, B
MySQL field type and length optimisation
Moderator: General Moderators
Re: MySQL field type and length optimisation
After a bit of digging around in the MySQL manual it seems that the length specified in parentheses after *INT is only the display size. Values greater than that length still get stored.
http://dev.mysql.com/doc/refman/5.0/en/ ... types.html
1) So when creating *INT columns in my tables, should I specify the length or just leave it as the default?
TINYINT... default is 3
SMALLINT... default is 5
MEDIUMINT... default is 8
INT... default is 10
etc
Does it make any difference from a storage/performance point of view?
Or should I just focus on always getting the correct range (TINY/SMALL/MEDIUMINT) for the values I want to store?
2) I notice that when I create a BOOL field with phpMyAdmin that it actually gets created as a TINYINT(1)
Is that something to worry about or should I leave it?
3) If I create a column INT(11), I guess the maximum value it would ever accept/store would only be 10 digits in length... the maximum value of UNSIGNED INT is 4294967295
Is that correct?
Thanks, B
http://dev.mysql.com/doc/refman/5.0/en/ ... types.html
1) So when creating *INT columns in my tables, should I specify the length or just leave it as the default?
TINYINT... default is 3
SMALLINT... default is 5
MEDIUMINT... default is 8
INT... default is 10
etc
Does it make any difference from a storage/performance point of view?
Or should I just focus on always getting the correct range (TINY/SMALL/MEDIUMINT) for the values I want to store?
2) I notice that when I create a BOOL field with phpMyAdmin that it actually gets created as a TINYINT(1)
Is that something to worry about or should I leave it?
3) If I create a column INT(11), I guess the maximum value it would ever accept/store would only be 10 digits in length... the maximum value of UNSIGNED INT is 4294967295
Is that correct?
Thanks, B
Re: MySQL field type and length optimisation
If there is even a single column of type varchar() don't bother to use char(*) - every char(*) is transformed to a varchar(*).batfastad wrote:I also changed a few VARCHARS to CHARS of specific length, for columns like currency/country codes which are wither 3/2 chars or NULL.
Key length should be shorter - that is performance should be better.batfastad wrote:Does it make any difference from a storage/performance point of view?
I think INT(N) means binary numeric type with N bits length. Boolean is 1 bit.batfastad wrote:2) I notice that when I create a BOOL field with phpMyAdmin that it actually gets created as a TINYINT(1)
Is that something to worry about or should I leave it?
See my previous answerbatfastad wrote:3) If I create a column INT(11), I guess the maximum value it would ever accept/store would only be 10 digits in length... the maximum value of UNSIGNED INT is 4294967295
Is that correct?
There are 10 types of people in this world, those who understand binary and those who don't
Re: MySQL field type and length optimisation
Hi
Thanks for the reply.
I've decided to switch to SMALL/MEDIUMINT where appropriate for my primary keys.
But I'm a bit confused about the use of the length parameter
Having had a read through the manual at http://dev.mysql.com/doc/refman/5.0/en/ ... types.html it seems the length parameter is actually a "display length"
Even if you specified INT(4) (or SMALLINT(4)) the field would still store up to the maximum integer value
It seems the length in parentheses is only used if the ZEROFILL attribute is used, where the value will be padded to the length with zeros.
1) Is that what you understand from reading that?
In that case I'll be leaving the length values to the defaults that are created when I create SMALL/MEDIUM/INT for unsigned
(SMALLINT is 5, MEDIUMINT is 8, INT is 10)
2) NULLs. I've read that there's very little performance penalty for using NULL values. But should I allow and store them? Or leave my field values just blank rather than setting them to NULL?
For my BOOL (TINYINTs) they are either 1 or NULL
From a bit more reading it seems semantically NULL should be used for when a value is not known and blank where the user specifically knows there is no value for that field.
At the moment in my tables I set any fields where there's no data been entered (eg phone or fax) to NULL rather than leaving them as blank values. Is that really bad practice and something I should worry about?
Cheers, B
Thanks for the reply.
I've decided to switch to SMALL/MEDIUMINT where appropriate for my primary keys.
But I'm a bit confused about the use of the length parameter
Having had a read through the manual at http://dev.mysql.com/doc/refman/5.0/en/ ... types.html it seems the length parameter is actually a "display length"
Even if you specified INT(4) (or SMALLINT(4)) the field would still store up to the maximum integer value
It seems the length in parentheses is only used if the ZEROFILL attribute is used, where the value will be padded to the length with zeros.
1) Is that what you understand from reading that?
In that case I'll be leaving the length values to the defaults that are created when I create SMALL/MEDIUM/INT for unsigned
(SMALLINT is 5, MEDIUMINT is 8, INT is 10)
2) NULLs. I've read that there's very little performance penalty for using NULL values. But should I allow and store them? Or leave my field values just blank rather than setting them to NULL?
For my BOOL (TINYINTs) they are either 1 or NULL
From a bit more reading it seems semantically NULL should be used for when a value is not known and blank where the user specifically knows there is no value for that field.
At the moment in my tables I set any fields where there's no data been entered (eg phone or fax) to NULL rather than leaving them as blank values. Is that really bad practice and something I should worry about?
Cheers, B
Re: MySQL field type and length optimisation
Hi guys
Anyone got any info on what I should be doing with NULLs?
Cheers, B
Anyone got any info on what I should be doing with NULLs?
Cheers, B