Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What size INT should I use for my autoincrement ids MySQL

Currently we're using INT(21)* for all autoincrement id columns in out 30+ table database.

We are a blogging site, and have tables storing members, comments, blog posts and the like.

I'm quite sure we will never reach the limit of our INT(21) id columns, and would like to know:

  • If using INT(21) when I am sure we'll never need it is a waste of space
  • If it is a waste, what the recommended size for an autoincrement id column is

*Not my design. I'm asking this because I'm considering reducing this to say, INT(10).

like image 420
Michael Robinson Avatar asked Aug 25 '10 03:08

Michael Robinson


People also ask

How do I set Autoincrement value?

In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.

What is integer primary key autoincrement?

Summary. The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed. In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.

Should I use auto increment ID?

Auto-increment should be used as a unique key when no unique key already exists about the items you are modelling. So for Elements you could use the Atomic Number or Books the ISBN number.

Is Autoincrement primary key?

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.


2 Answers

The value within the brackets is the display width.

[It] 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 INT(3) has the usual INT range of -2147483648 to 2147483647, and values outside the range permitted by three characters are displayed using more than three characters.

Conclusion

INT(10) or INT(21), doesn't impact the values that can be stored. If you really have a concern, the data type can easily be changed to be BIGINT with no repercussions that I'm aware of. I'd look at how many new records are being created in a given period (IE a month) & see how long it'll take to max out the INT value based on that history.

like image 126
OMG Ponies Avatar answered Oct 02 '22 11:10

OMG Ponies


See here for the limit of each int type: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Note that INT(21) == INT(100000). The number in brackets is just how many zeros are padded to it if you specify the field should be zero-padded.

An unsigned int field can hold up to 4294967295 records (see link above).

like image 20
Amy B Avatar answered Oct 02 '22 09:10

Amy B