Primary key auto increment best practices

Question

Apologies if something similar has already been asked, I wasn't sure of the best term to search for to get what I was after, so please let me know if there is already a bunch of other related questions and I'll close it!

To the problem!

I have a MySQL database table that has an auto incrementing primary key, the rows in this table get removed and re-added in stages automatically once a day via a cron job php script on a web server.

This script removes and re-adds about 110,000 rows a day, and therefore the primary key number increases by the same number each day. It's currently on about 4 million.

With my limited knowledge of databases, I'm not sure if this is bad practice or not. Years down the line this number is going to be huge! Can it actually get too big? It's currently a BIGINT field type.

I don't have any programmatic need to have this unique primary key - I won't ever be searching based on this number, however it is the only unique Identifier per row.

I can't just reset the index because the whole table is not truncated - only part of it per product category.

What's the best (most efficient) way to handle this scenario?

Answer 1

You're using a BIGINT which is 8 bytes. The maximum value you can store in a BIGINT is 18446744073709551615 if unsigned. At your rate of insertion of approximately 100 thousand rows per day, it will take you 459445680541 years to overflow.

In short, you have nothing to worry about.

In fact, you should consider changing this field to INT which is 4 bytes, with maximum value of 4294967295, which you can use for approximately 110 years before overflowing.

Answer 2

If you have a UNIQUE KEY (other than the id), use INSERT...ON DUPLICATE KEY UPDATE (without specifying id). That will UPDATE when there are changes without "burning" an id, and it will INSERT when the row is new. This also avoids removing the 110K rows.

Do you have any use for the id? If not, and you do have a UNIQUE KEY, suggest you simply make that the PRIMARY KEY.

If you are replacing ALL the rows in the table, and you don't need to preserve old values for an AUTO_INCREMENT, then this works better:

• CREATE TABLE new LIKE real;
• INSERT INTO new ... (or) LOAD DATA...
• RENAME TABLE real TO old, new TO real;
• DROP TABLE old;

That will let you do the replacement with zero downtime.