Is it a good solution to remove primary key column (id int) after it reaches the maximum value?-CodePudding

Let's say I have a table.

create table hour_ranks(
id int not null auto_increment, 
hour_int int not null comment 'hour of that ranks, for example 2022100108 which means  October 1st, 8 oclock,2022',
audio_id int not null comment 'from audio table',
PRIMARY KEY (`id`),
UNIQUE KEY (`audio_id`,`hour_int`),
)

I shipped some code a month ago without realizing that, while 10 million entries may be added daily, only a few thousand are actually helpful, and 99.99% are erased. And then the system crashes because no records can be added after the id field reaches its maximum value of 21xxxxxxxx. We also immediately fixed the issue by switching the "id" column's type to "bigint."

Since the "bigint" method is only a short-term fix, I come up with another solution:

Remove the "id" column, and I think mysql server will try to use that unique key as a primary key. And apparently I need to change my code accordingly.

Is it a good practice to remove an existing primary key column, and how much does the performance of searching, inserting, etc. after deleting the existing primary key change?

Any ideas would be appreciated.

CodePudding user response：

Since the "bigint" method is only a short-term fix...

An int maxes out at 2,147,483,648. At 10 million a day you'll run out in 214 days.

A bigint maxes out at 9,223,372,036,854,775,808. At 10 million a day you have 922,337,203,685 days or 2,526,951,242 years.

Is it a good practice to remove an existing primary key column

Only if nothing refers to that table, if it has no foreign keys. Without a primary key it is difficult to join with other tables and also maintain referential integrity.

If you don't need to refer to the table, change UNIQUE KEY (audio_id,hour_int) into your primary key.

That said, it's always a good idea to have a simple primary key.

CodePudding user response：

Max value of signed bigint in Mysql is (2^63)-1. It's a huge number.

But if you are going to add millions of rows to your table every hour/day you should think about another database. Because at some point your table may start to degrade (in terms of performance), I suppose.