what datatype to use for MySQL unique key/index

Question

Is it ok to use INT as the datatype for the index in MySQL. I'm not sure if this is the best way to go as I fully expect this table to get a lot of entries that will eventually 'overflow' the INT

I doubt something like Facebook uses INT for their index on something such as the wallpost table or something else that would be high traffic / get lots of entries.

Any thoughts?

An index speeds up data retrieval; I think you meant to say "primary key" instead. — OMG Ponies, Mar 10 '11 at 06:19

score 2 · Accepted Answer · answered Mar 10 '11 at 06:18

2

use INT( 11 ) UNSIGNED ZEROFILL NOT NULL

answered Mar 10 '11 at 06:18

xkeshav

53,360
44
177
245

The prefix on INT only affects the displayable characters, not the size. – OMG Ponies Mar 10 '11 at 06:19
Why is this the best option? That is still only a finite number. – john Mar 10 '11 at 06:22
@john how many rows will be there???? The `unsigned INT `range is 0 to 4294967295.... is it not enough?? – xkeshav Mar 10 '11 at 06:24
fair point. I don't think I will have over 4 billion entries in this table – john Mar 10 '11 at 06:32
its same but more efficient way – xkeshav Mar 10 '11 at 06:54
i asked [here](http://stackoverflow.com/questions/5256469/what-is-the-benefit-of-zerofill-in-mysql) – xkeshav Mar 10 '11 at 07:00

score 1 · Answer 2 · answered Mar 10 '11 at 06:24

1

An INT can go up to over 2 billion (twice that many if unsigned). If you expect your table to have more rows than that, you can use a BIGINT (which unsigned goes up to 18,446,744,073,709,551,615—probably more than even Facebook needs).

answered Mar 10 '11 at 06:24

Ted Hopp

232,168
48
399
521

score 1 · Answer 3 · edited May 23 '17 at 12:17

Generally speaking, you have two choices:

A 'natural key' that reflects the reality of the data you are working with. For example, a telephone number might be a valid primary key for a calling list, but it would not be appropriate at all for, say, a list of health care beneficiaries because many people might share the same phone number in a home. Note that a natural key might be made up of more than one field. For example, the combination of Make, Model, and Year might be a key for a list of automobile models.

A 'surrogate key' on the other hand, is just an arbitrary value that you assign to a row. If you go that route, I'd recommend using a GUID (UUID in MySql). The best way that I know to represent those in MySQL is with a char(36) column. GUIDs are effectively unique forever and can be used infinitely.

If you insist on using a plain old number, then INT is probably fine, or you can use BIGINT to be really sure.

BIGINT UNSIGNED ZEROFILL

what datatype to use for MySQL unique key/index

3 Answers3