What if 2^32 is just not enough?

Question

what if you have so many entries in a table, that 2^32 is not enough for your auto_increment ID within a given period (day, week, month, ...)?
What if the largest datatype MySQL provides is not enough?

I'm wondering how should I solve a situation where I'm having so many entries added to my table which require unique ID, but I fill up my datatype within one period?

How could I natively within, MySQL (or any other system), achieve unlimited amount of unique IDs or at least increase it exponentially?

Ideally I would expect something like

> SELECT * FROM table;

+---+------+
| a |  b   |
+---+------+
| 1 |  1   |
| 1 |  2   |
| 1 |  3   |
|...| .... |
|...| .... |
| 1 | 2^32 |
| 2 |  1   |
| 2 |  2   |
+---+------+

Which exponentially increases the amount of entries.

How do you cope with such situations?
Remember - requirement is to have unique ID for any entry.

Interested to know what kind of data would require such high IDs — David Snabel-Caunt, Mar 31 '09 at 23:11

Stefan Gehrig · Answer 1 · 2009-03-31T22:27:22.957

Don't you think a BIGINT UNSIGNED would be sufficient? That's a range of 0 - 18.446.744.073.709.551.615, or one year with 50.539.024.859.478.223 entries per day (365 d/y), 2.105.792.702.478.259 entries per hour, 35.096.545.041.304 entries per minute or 584.942.417.355 per second.

With assumed 600 writes per second (without any reads) you could write entries 974.904.028 years at full write speed. That should be enough.

Jeremy CD · Accepted Answer · 2009-04-01T04:03:00.997

12

You could use BIGINT for the primary key. This is a 64-bit number by default.

Edit #2: Apparently what I said before about varying the BIGINT byte length was incorrect. BIGINT is fixed at an 8-byte limit.

edited Apr 01 '09 at 04:03

answered Mar 31 '09 at 20:49

Jeremy CD

597
4
10

http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html implies that BIGINTs are fixed at 8 bytes? – Rowland Shaw Mar 31 '09 at 20:55
Actually what Rowland says is correct - a BIGINT is always 64bit. The number that can be associated with numeric datatypes only define the display width and have NO impact on the storage capacity. – Stefan Gehrig Mar 31 '09 at 22:25
I'm confused 8 byte and 64 bit are the same thing right? so then whey the edit? – Fire Crow Apr 01 '09 at 12:33
1

Typically, 8 bytes and 64 bits are equivalent, and this is a typical case. I edited because I also suggested that BIGINT(n) varied the byte length, which is incorrect. – Jeremy CD Apr 01 '09 at 12:52

score 7 · Answer 3 · answered Mar 31 '09 at 20:47

7

Just use 128-bit keys. There is no need for an unlimited number of keys, since you very quickly allow more rows than the number of atoms in the universe. (somewhere around 256 bits).

answered Mar 31 '09 at 20:47

Eclipse

44,851
20
112
171

score 7 · Answer 4 · answered Mar 31 '09 at 22:13

If you have so much data that you encounter this problem, then choosing a primary key is probably the least of your concerns.

If you're using the InnoDB engine, it may be helpful for performance to choose a primary key that you'll be frequently searching on (especially where the searches return many rows), as it clusters the primary key, which makes range scans better.

score 5 · Answer 5 · answered Mar 31 '09 at 20:51

5

I'd start by moving to BIGINT for 2^64. GUIDs would be another option, but you need to store these yourself in "some form"

answered Mar 31 '09 at 20:51

Rowland Shaw

37,700
14
97
166

score 2 · Answer 6 · answered Mar 31 '09 at 20:48

2

Don't use an autoincrementing primary key - use a GUID or similar - from the Wikipedia article:

While each generated GUID is not guaranteed to be unique, the total number of unique keys (2^128 or 3.4×10^38) is so large that the probability of the same number being generated twice is infinitesimally small. For example, consider the observable universe, which contains about 5×1022 stars; every star could then have 6.8×1015 universally unique GUIDs.

answered Mar 31 '09 at 20:48

Rich

15,602
15
79
126

Didn't think MySQL had native support for GUIDs though? – Rowland Shaw Mar 31 '09 at 20:52
1

Then get a RDMS that can handle what you need. – Eclipse Mar 31 '09 at 20:54
3

Look up "birthday paradox". You'll actually have about a 50% chance to get the same GUID twice around the time you've generated 2^64 GUIDs. So it has no advantage over using a 64 bit autoincrementing type. – Michael Borgwardt Mar 31 '09 at 22:55
Depends on the algorithm you use to generate GUIDs. Most involve a time counter of some sort. – Pavel Minaev Sep 02 '09 at 19:43

score 1 · Answer 7 · answered Mar 31 '09 at 22:05

When you add another column to your key, you are effectively doubling the number of index scans you will need to perform (albeit on a much smaller index for the second column).

As stated earlier, your best bet for VAST data sets is either a GUID (if your RDBMS supports it natively) or a varchar(16).

The nice part about using a varchar / varbinary is that you could automatically expand the column in the future, if needed. And the bad part is that varchar / varbinary is a poorly performing key, compared to an integer.

Daniel Rotnemer · Answer 8 · 2020-02-04T21:03:29.967

0

If BIGINT is not sufficient for you, use it in your table and when the amount of entries reach the BIGINT border, create another table and start off again from 0. Now you will have 2 tables to store the same type of data.

edited Feb 04 '20 at 21:03

answered Feb 04 '20 at 12:53

Daniel Rotnemer

107
3
12

score 0 · Answer 9 · answered Mar 31 '09 at 20:48

0

I'm not sure how to generate them automatically in MySQL, and then, they wouldn't necessarily be sequential, but I'm pretty sure that you could use a GUID and not have to worry about them filling up.

answered Mar 31 '09 at 20:48

dustyburwell

5,755
2
27
34

score 0 · Answer 10 · answered Mar 31 '09 at 20:52

0

You could also use chars/varchars for your key columns, and use GUIDs for your keys. I don't know if that would incur a performance penalty when compared to integer primary keys though.

answered Mar 31 '09 at 20:52

Kevlar

8,804
9
55
81

What if 2^32 is just not enough?

10 Answers10