BIGINT(50) or INT UNSIGNED for InnoDB?

[random1]

I'm working on a large MySQL database.

 

The focus and requirement is allowing as many records as reasonable.

 

It's running on a 64-bit, 4 processor dedicated server. I've chosen InnoDB for the storage engine.

 

It currently has all primary keys on all tables being of type BIGINT(50) UNSIGNED and AUTOINCREMENT.

 

I've read that InnoDB uses it's own indexing and having a large primary key effects performance.

 

Is it fine for me to use BIGINT(50) UNSIGNED for primary keys?

 

Should I be using INT UNSIGNED?

 

Does BIGINT(50) cause problems on 32-bit machines?

[oni-kun]

BIGINT is stored in 8 bytes, from -2^63 (-9,223,372,036,854,775,808) to 2^63. I have a small inkling that it will not work on 32 bit arches.

 

Now the question is, What indexes require.. I'm not sure the prefex, Yottoillian, Keys?

 

Unsigned INT's go to 2,147,483,647, which is exactly 32 bits. So probably that is the best choice.

[Mchl]

Unsigned INT go up to 2^32-1 = 4 294 967 295. If you don't expect your table to have more records than that, don't use anything larger. If you expect your tables to be larger than 4 billion rows, you probably need to hire a professional DBA and invest into a datacenter.

BTW oni-kun, 2 147 483 647 is for signed integers.

 

The thing with InnoDB is that all indexes in InnoDB tables will also contain primary key, so the larger datatype used for primary key column, the larger ALL of your indexes become. On the other hand additional 4 bytes is not all that much. It's more important not to create your primary key on VARCHARs and such.

 

BIGINT does not cause any problems on 32bit machines. Also note that BIGINT(50) is nonsensical, because the largest number stored in BIGINT (18 446 744 073 709 551 615) has only 20 digits (and besides, this number is only used for output formatting, not for defining storage capacity).