59

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
  • 54,938
  • 169
  • 450
  • 748
  • 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

3 Answers3

88

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
  • 54,048
  • 11
  • 129
  • 129
  • 11
    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
20

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
  • 43,890
  • 13
  • 88
  • 135
1

Why does anybody speak about it as it would not make any difference to use one of them.

The bigger your database get, the more time it'll take to iterate through all these data.

Imagine that someone hides your car key in a bag. The bag is in one the 20 boxes in front of you and each of them contain 8 bags in total.

This would take a lot of time since you have to check 8 bags in 20 boxes in a worst case scenario, which is 160 bags you have to check for.

Now halve the amount of bags inside the boxes to 4. You'll definitely save time since you have to check 80 bags at the maximum.

Well the same practice goes for databases. If you use bigint instead of int (or medium,small,tiny) you'll experience a performance drop down. That's why you should choose your datatypes wisely.

Sercan Samet Savran
  • 755
  • 1
  • 9
  • 20
  • I perform many bulk updates where duplicate values are ignored. Very quickly, the last 100.000th entry of my table has an `id` around 1.000.000. Probably there are more effective ways to perform the update but that is a case. – raratiru May 17 '23 at 16:01