0

I have table in which we are inserting too many records per day. The table has a long columns as a primary key. Now in past 2 year its already exhausted 50% of the LONG range. (Java's LONG maps to MySQL's BIGINT.) I am looking for other data which we can use as a primary key. what can we the other options, What I am think of creating a new table, with similar structure for a non-sequencial primary key. But I dont have any idea how to achieve this without performance it.

What I am think of creating a new table, with similar structure for a non-sequencial primary key. But I dont have any idea how to achieve this without performance it.

Rick James
  • 135,179
  • 13
  • 127
  • 222
dead programmer
  • 4,223
  • 9
  • 46
  • 77
  • Primary key can also be of multiple columns. – prasad_ Apr 26 '23 at 03:30
  • @prasad_ by my primary is referenced in other table. I cannot embed composite key in the other table for reference – dead programmer Apr 26 '23 at 03:36
  • Also see these Stack Overflow posts: [MySQL primary key column type for large tables](https://stackoverflow.com/questions/816646/mysql-primary-key-column-type-for-large-tables) and [MySQL PRIMARY KEYs: UUID / GUID vs BIGINT (timestamp+random)](https://stackoverflow.com/questions/6338956/mysql-primary-keys-uuid-guid-vs-bigint-timestamprandom). – prasad_ Apr 26 '23 at 04:24
  • 3
    There is no LONG data type. For an integer type for very large tables, use unsigned BIGINT, which allows up to 18446744073709551615, which should be sufficient for almost any purpose. – ysth Apr 26 '23 at 04:34
  • MariaDB 10.4 (2019) will automatically provide a HASH for uniqueness. `UNIQUE KEY ... USING HASH`. – Rick James Apr 27 '23 at 02:36
  • 1
    "exhausted 50% of the LONG range" -- Are you talking about C 32-bit "long" datatype? – Rick James Apr 27 '23 at 02:38
  • @ysth yes yo are right. I am using JAVA JPA which is convert java LONG to mysql BIGINT data type. both are equal – dead programmer Apr 27 '23 at 13:12
  • 2
    there's no way you have used 50% of the BIGINT range in 2 years; that would take 14 billion records per second. – ysth Apr 27 '23 at 15:58
  • 1
    please edit your question to show (as text, not image) output of `show create table yourtablename` – ysth Apr 27 '23 at 15:59

1 Answers1

1

If you are saying that your 32-bit INT is threatening to overflow, then the simplest solution is to

ALTER TABLE tablename MODIFY id BIGINT NOT NULL AUTO_INCREMENT

It will take a loooooong time if there are a billion rows.

(If you mean something else by "LONG", please explain.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • As I explained in the problem my primary its already a LONG and its range is about to finish in next 7-8 months. so I am looking for alternative for the same. So Java LONG datatype is transfered to BIGINT of mysql – dead programmer Apr 27 '23 at 13:10
  • Are you saying that Java's LONG is a 64-bit datatype? (I haven't touched Java in 24 years.) What do you get from `SELECT MAX(id) FROM tbl`? – Rick James Apr 27 '23 at 14:58
  • 45560427 , table is just 3 month old. – dead programmer Apr 28 '23 at 05:09
  • @deadprogrammer - At that rate, it will take more than a decade to overflow MySQL's `INT SIGNED`. Unless you are working for the govt, you will rewrite the app before then. – Rick James Apr 28 '23 at 18:56
  • @deadprogrammer - Another thing -- How many rows in the table (`SELECT COUNT(*) FROM tbl`)? If it is a lot less than 45560427, then you are deleting and/or burning ids. In that case, tell me more; I may have ways to avoid growing the ids so fast. – Rick James Apr 28 '23 at 18:58
  • table has insert and update operations only. There is no delete operation. – dead programmer May 05 '23 at 05:21
  • Yeah, but `INSERT IGNORE` can "burn" ids. So there _may_ be fewer than 45560427 _rows_. – Rick James May 05 '23 at 14:55