55

I have been using int(10) and just noticed that Wordpress uses bigint(20) - What is different to use bigint(20) and int(10) for id auto increment? Which one should I use for id column?

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

Vs

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

Thanks.

Run
  • 51,293
  • 159
  • 419
  • 719
  • the question is, if it is less performant: http://stackoverflow.com/questions/9376610/bigint-mysql-performance-compared-to-int – rubo77 Feb 21 '12 at 11:42

2 Answers2

80

The difference is purely in the maximum value which can be stored (18,446,744,073,709,551,615 for the bigint(20) and 4,294,967,295 for the int(10), I believe), as per the details on the MySQL Numeric Types manual page.

Incidentally, the use of (20) and (10) is largely irrelevant unless you're using ZEROFILL. (i.e.: It doesn't actually change the size of the number stored - that's all down to the type.)

However, in practical terms it should be noted that you're not likely to hit either of these limits any time soon, unless you're a really active blogger.

John Parker
  • 53,316
  • 11
  • 128
  • 128
  • 7
    It should be noted that bigint consumes 8 bytes of storage vs 4 bytes for int. A signed (mysql default) int allows values > 2 billion; therefore, the vast majority of the time it is an excellent candidate for the id column. Of course, unless you're actually dealing with huge record sets where column type/storage is a concern, bigint vs int is not terribly important – virtualeyes Aug 26 '11 at 20:31
17

The only difference is the range of the type. INT is a 32-bit long while BIGINT is 64-bit long, therefore it can store much larger numbers like 123456789123456789 (which cannot be stored as INT).

Here's a full list of MySQL integer types: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Crozin
  • 42,946
  • 13
  • 87
  • 135