MySQL field type and length optimisation
Posted: Tue Nov 03, 2009 3:44 am
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
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