datatype size vs performance

[chandimak]

I need to know whether there is a performance issue when dealing with the database(database operations)depending on the datatype size of a particular field of a table. I have a table with a SMALLINT size primary key. But there is a chance of having a big data range than this data type can hold later.

I thought to allocate SMALLINT at the moment and later change it to MEDIUMINT depending on the required size. I thought to do this as I highly require the system to perform faster and because I thought datatype would affect the performance.

It's highly appreciated if someone could explain the relationship between datatype size and performance.

[asaukani]

of course there is a differences in performance
but in my opinion, unless your application is really mission critical where second counts and u have hundred thousand of records and may be more than one hundred tables u don't need to be worried about performance

ahmad

[debasisdas]

changing the data type of a field is a bad idea.

[urtrivedi]

Even some database do not allow to change type especially when table having data, or it is key column.

In your case if it is PK then when u change PK's datatype you will also have to change datatype of its foreign key reference.

I suggest if it is going to cross SMALLINT range then better to use MEDIUMINT in the begining.

[chandimak]

Thank you all for your valuable explanations. When I further went in to designing facts I found that I will not store signed values and I can manage the data range with SMALLINT UNSIGNED. I further searched for datatype size vs performance issues and found the following which I think is of great value when designing a database.

http://verysimple.com/2006/10/22/mysql-data-type-optimization-tips/