What size INT should I use for my autoincrement ids MySQL

mysql primary-key auto-increment
Michael Robinson picture Michael Robinson · Aug 25, 2010 · Viewed 24.8k times · Source

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).

Answer

OMG Ponies picture OMG Ponies · Aug 25, 2010

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.

Related questions

make an ID in a mysql table auto_increment (after the fact)

I acquired a database from another developer. He didn't use auto_incrementers on any tables. They all have primary key ID's, but he did all the incrementing manually, in code. Can I turn those into Auto_incrementers now? Wow, very …

Read More
Auto Increment after delete in MySQL

I have a MySQL table with a primary key field that has AUTO_INCREMENT on. After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state …

Read More
MySQL: #1075 - Incorrect table definition; autoincrement vs another key?

Here is a table in MySQL 5.3.X+ db: CREATE TABLE members` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `memberid` VARCHAR( 30 ) NOT NULL , `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `firstname` VARCHAR( 50 ) NULL , `lastname` VARCHAR( 50 ) NULL , UNIQUE (memberid), PRIMARY KEY (id) ) …

Read More