CODEWITHSUNDEEP
mysqldatabase-designintbigintegerauto-increment
Run
Run

Reputation: 57176

MySQL: bigint Vs int

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.

Upvotes: 60

Views: 93733

Answers (4)

Yopy Hi del Cano
Yopy Hi del Cano

Reputation: 1

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.

Upvotes: 0

Sercan Samet Savran
Sercan Samet Savran

Reputation: 927

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.

Upvotes: 2

John Parker
John Parker

Reputation: 54415

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.

Upvotes: 90

Crozin
Crozin

Reputation: 44376

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

Upvotes: 20

Related Questions