What is the performance difference between BIGINT and INT in MySQL?

7

Is there a negative impact on the performance of MySQL, where the Primary Key is of type BIGINT(20) , instead of INT(11) ?

    
asked by anonymous 10.07.2015 / 00:51

2 answers

12

What is the difference between INT and BIGINT ?

Let's return to the manual first, at 10.2.1 Tipos Inteiros , we have the following:

INT[(M)] [UNSIGNED] [ZEROFILL]
  

An integer of normal size. The signed interval is between -2147483648 to 2147483647.   The unsigned range goes from 0 to 4294967295.

BIGINT[(M)] [UNSIGNED] [ZEROFILL]
  

A large integer. The signed interval is between -9223372036854775808 and 9223372036854775807.   The unsigned range goes from 0 to 18446744073709551615.

Okay, then a INT can allocate values up to 2.1 billion and a BIGINT can allocate any value up to 20 dígitos .

So, what does it matter?

A lot, actually. Using INT instead of BIGINT can significantly reduce disk usage. If only this option of INT instead of BIGINT can save you from 10% to 20% of disk space (depending on the situation). Better yet, if used as chave primária and as chaves estrangeiras and indexes , the reduction of its index can reach 50% and with this, promote the performance when these indexes are used .

    
10.07.2015 / 01:19
9

Essentially there is not. It may even have some marginal difference but nothing that is important and will even be difficult to measure, the more it disrupts something. And if there is, it will be more by consequences of size than by type itself.

That said, you will hardly need a BIGINT for a primary key. If not, do not use. And if you need to, then you should use it even if the performance is worse.

Obviously you should not use anything larger than you need, saving space is always a good thing if it is not at the cost of something else.

So use what you need and do not worry about performance in cases like this.

In the database, small performance gains are completely irrelevant. It's the worst form of micro-optimization.

    
10.07.2015 / 00:59