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

Question

There is some negative impact on the performance of Mysql, where the Primary Key is the type BIGINT(20), instead of INT(11)?

Answer 1

What’s the difference between INT and BIGINT?

Let’s go back 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 range is from -2147483648 to 2147483647. Unsigned range from 0 to 4294967295.

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

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

Okay, so, um, um 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 INT instead of BIGINT can make you save 10% to 20% of disk space (depending on the situation). Better yet, if used as chave primária and how chaves estrangeiras and indexes, the reduction of its index can reach 50% and with this, promote performance when these Dexes are used.

Answer 2

Essentially there is no. It may even have some marginal difference but nothing that is important and even difficult to measure, let alone hinder something. And if there is, it’ll be more consequences of size than the guy himself.

That said, you hardly need a BIGINT to a primary key. If you don’t need it, don’t use it. And if you do, 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 another important thing.

So use what you need and don’t worry about performance in cases like this.

In databases, small performance gains are completely irrelevant. It is the worst form of micro optimization.

Answer 3

The difference is in relation to the allocation of space in the database, consequently in the use of disk, RAM, cache memory, data transfers, network etc.

While INT allocates 4 bytes, BIGINT allocates double, 8 bytes.

Use BIGINT only if there is a need to store values greater than 4294967295 (unsigned) or 2147483647 (Signed).