3

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?

9

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.

  • Thank you for the reassurance - I think that's more than enough time for this. Probably won't change the field type, as the even though the usual rate of additional rows is about 110,000, this cron job script can be run manually by a user, potentially quite a few times during the day as well, and disk size is not a concern. The table only hovers around the 20MB mark. – Novocaine Feb 11 '15 at 9:47
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.

  • This is the only unique key in a row, so I can't follow your first suggestion. However I am intrigued by your second suggestion. I'll have to give that a go, as I delete a product before I re-insert it with the fresh data, there is some downtime - not much, but the closer to zero that I can get, the better. One question with this method - as I'll be renaming this temporary table to the same as the old table, all user permissions will remain the same right? – Novocaine Feb 12 '15 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.