Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

BIGINT mysql performance compared to INT

Tags:

mysql

I'm trying to find out if my table will get less performant if I change the primary key to BIGINT(20). At the moment, I'm using INT(7) and have about 300.000 entries already with large IDs (7 or 8 digits). I searched a lot already but only found out that it uses more disk-space (which is obvious).

All of my IDs have 7 digits right now, but my customer wants to change to 8 digits. I won't be able to easily change the software in the future, so I thought about using BIGINT(20) now just in case. Would it be less performant if I use BIGINT even though I don't need to yet?

Does anyone with experience doing this have suggestions regarding speed and performance?

like image 835
rubo77 Avatar asked Feb 21 '12 11:02

rubo77


People also ask

Should I use INT or BIGINT?

In any decent sized database you will run into problems with INT at some stage in its lifetime. Use BIGINT if you have to as it will save a lot of hassle further down the line. I have seen companies hit the INT issue after only a year of data and where reseeding was not an option it caused massive downtime.

What is the difference between INT and BIGINT in MySQL?

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 ).

Should I use INT or BIGINT for primary key?

You can use BIGINT as a primary key but with some penalties. BIGINT takes up more space on disk storage than INT and using BIGINT as a primary key (or any index) will add size to the index, perhaps as much as doubling it. This can have a performance impact on searching the index and make it slower to run queries.

Is BIGINT same as integer?

Remarks. The int data type is the primary integer data type in SQL Server. The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type. bigint fits between smallmoney and int in the data type precedence chart.


2 Answers

To answer your question: yes it'll get less performant. Obviously, the bigger the type, the bigger the table, the slower the queries (more I/O, bigger indexes, longer access time, result less likely to fit in the various caches, and so on). So as a rule of thumb: always use the smallest type that fits you need.

That being said, performance doesn't matter. Why? Because when you reach a point where you overflow an INT, then BIGINT is the only solution and you'll have to live with it. Also at that point (considering you're using an auto increment PK, you'll be over 4 billions rows), you'll have bigger performance issues, and the overhead of a BIGINT compared to a INT will be the least of your concerns.

So, consider the following points:

  • Use UNSIGNED if you don't need negative values, that'll double the limit.
  • UNSIGNED INT max value is 4.294.967.295. If you're using an auto increment PK, and you only have 300.000 entries, you really don't need to worry. You could even use a MEDIUMINT at the moment, unless you're planning for really really fast growth. (see http://dev.mysql.com/doc/refman/5.1/en/integer-types.html)
  • The number in parenthesis after the type doesn't impact the max value of the type. INT(7) is the same as INT(8) or INT(32). It's used to indicate the display width if you specify ZEROFILL (see http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html)
like image 79
rlanvin Avatar answered Sep 29 '22 03:09

rlanvin


Not wishing to resurrect a zombie, but 'modern' mysql uses the column type serial, which is a bigint(20) unsigned NOT NULL AUTO_INCREMENT - and certainly suggests that mysql will be (or is) going to be optimised for using bigint as a primary key.

Also, rather than using serial, varbinary(16) primary allows for one (we do this) to use uuid_short() for the primary key (not uuid – which is very slow to use as a primary, because it's a string) - which has the feature of ensuring that every record has a key which is unique across the entire database (indeed - network).

But be aware - some coercion’s will degrade bigint to int with bad results. If, for instance, you are comparing a string representation with a big int - you may find that you get false positives. So one must compare using binary... eg

where id = binary id_str 

Personally I would call this an unfixed bug...

like image 22
Konchog Avatar answered Sep 29 '22 04:09

Konchog