MySQL primary/foreign key size?

Question

I seem to see a lot of people arbitrarily assigning large sizes to primary/foreign key fields in their MySQL schemas, such as INT(11) and even BIGINT(20) as WordPress uses.

Now correct me if I'm wrong, but even an INT(4) would support (unsigned) values up to over 4 billion. Change it to INT(5) and you allow for values up to a quadrillion, which is more than you would ever need, unless possibly you're storing geodata at NASA/Google, which I'm sure most of us aren't.

Is there a reason people use such large sizes for their primary keys? Seems like a waste to me...

Answer 1

The size is neither bits nor bytes. It's just the display width, that is used when the field has ZEROFILL specified.

and

INT[(M)] [UNSIGNED] [ZEROFILL] A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

See this explanation.

Answer 2

I don't see any good reason to use a number larger than 32-bit integer for indexing data in normal business-sized databases. Most of them have maybe millions of records (or that order of magnitude).