smallint,mediumint,int type length?

in my table

i created columns like below

city_id smallint(11) unsigned,
user_id mediumint (11) unsigned,
message_id int (11) unsigned,

inside of parantheses what means 11 if i write something less or big.

if i created
city_id smallint(5) unsigned,
user_id mediumint (5) unsigned,
message_id int (5) unsigned,

is there will be any effect in memory or performance. Shortly what is different.
i readed in mysql site but do not understant.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


 Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, because in these cases MySQL assumes that the data fits into the original column width.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
Hello phparmy,

The parameter is the amount of padding to give to the field.  So if you specify 5, then only numbers lower than 5 digits will get padded.  Numbers over that will be displayed as is from my understanding.  Doesn't impact the range of numbers used or memory needed to store the values.


Kevin CrossChief Technology OfficerCommented:
Ah, openshac linked it for you too.  The text in that post is from the linked manual for MySQL.  Hopefully that explains it.
The parenthesis () is simply a display width. A smallint would always be 2 bytes in the range -32768 to 32767.
A smallint(3) would display the number 99 as ' 99' or '099' depending on whether you used the optional extension attribute ZEROFILL. In the same example, 1234 would always be displayed as '1234'.
1234 in an type defined as smallint(11) would display '       1234' or '00000001234'.
Hope this helps
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.