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?
Best Answer
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.