MySQL field type and length optimisation

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL field type and length optimisation

Post by batfastad »

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
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL field type and length optimisation

Post by batfastad »

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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL field type and length optimisation

Post by VladSun »

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.
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:Does it make any difference from a storage/performance point of view?
Key length should be shorter - that is performance should be better.
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?
I think INT(N) means binary numeric type with N bits length. Boolean is 1 bit.
batfastad 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?
See my previous answer ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL field type and length optimisation

Post by batfastad »

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
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL field type and length optimisation

Post by batfastad »

Hi guys
Anyone got any info on what I should be doing with NULLs?

Cheers, B
Post Reply