MySQL: bigint Vs int

Question

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.

Guanine

the question is, if it is less performant: #9377110 – Topflight

Answer 1

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.

Harken

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 – Chlorophyll

Answer 2

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

Erikerika

Answer 3

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.

Curran

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. – Cuttie

At the same time, imagine you prepared everything for 4 bytes, all the database, the code program and everything, then one day you realize it's not enough and you need to store bigger numbers... That's worse – Circumstantiality

@Circumstantiality that is why my last sentence is to choose it "wisely" and the case you speak about doesn't seem to be a wisely done decision. Think always how many data sets are there already and how many are going to be there for the same data within the next 30 years. That should help you to make the decision. Think about car brands, there are about ~3200 car brands today and I assume, that this will probably not exceed the max range of an unsigned smallint (65,535) within the next 30 years. – Curran

Answer 4

It's interesting to see how most of the comments only express concern about the size. Even for Wordpress the use of BigInt is important.

Currently, the use of UUIDs is more common, because when we create sites we should think big. At the end, maybe I am not the only blogger on my page. Maybe my entire community wants to express themself. With multiple accesses it is safer to insert 64-bit UUIDs.

Doiron