0

So perhaps I don't know the technical name of the 'id' column which has autoincrement so if a similar question exists, I cannot find it. I was wondering about the limit of the number of rows in an InnoDB table and the id reference that goes with it, and what happens when the limit is reached (assuming it can be reached).

It's a risk I have to consider. I have a table which contains a series of tens to hundreds of records which can be added/removed regularly. Assuming my product reaches enormous success (?), assuming records are changed regularly, the id field will quickly tick by. Add a year or more, even five years...

I asked a similar question to this on stackoverflow some time ago and from what I recall I was just asked not to worry about it but nobody could direct me to a hard answer.

Anyone care to share?

Thanks...

Improve this question
3
  • 3
    Create a table with tinyint autoincrement column and start inserting rows. Tinyint is small enough to fill by hand so you can get the hard answer yourself. Then for your table just use unsigned bigint, filling that would take thousands of years
    – jkavalik
    Commented Jul 4, 2016 at 17:58
  • 1
    Once you exhaust your ID space your inserts will stop. This can be a hassle to track down if you don't consider it early in your troubleshooting. We have thousands of databases across a lot of servers so we run a script periodically that identifies auto inc columns via the information schema and then checks the max IDs
    – Bill Croft
    Commented Jul 4, 2016 at 19:28
  • @jkavalik I love your idea for its simplicity and will give it a try. Thanks!
    – fiprojects
    Commented Jul 4, 2016 at 20:49

2 Answers 2

Reset to default
2

Do not use GUIDs -- they have a serious performance problem at scale. (This is because of their randomness.)

Within 5 years (probably within 1 year), you will decide on some significant change and this 'last id' question will be moot.

You are "removing" regularly? Why? If you are doing a REPLACE or DELETE, you probably have some other unique key on which you are finding what to remove. Consider making that the PRIMARY KEY and eliminate the AUTO_INCREMENT.

I have a PRIMARY KEY on virtually every table I make, yet only about 1/3 are AUTO_INCREMENT.

Beware of INSERT IGNORE and REPLACE (and others) -- they pre-allocate AUTO_INCREMENT ids, but may not use them.

Improve this answer
1
  • Ok... Thanks for this... so i have more to learn... I use sha1 hashes as keys to identify my records and don't actually use the id column - however some months ago, I briefly tried creating my table without the id column and got an error - I did not try figure out why (nor can I recall the error) I just put the id field back in. From what you are telling me, I just need enhance my learning on table creation a bit more.
    – fiprojects
    Commented Jul 12, 2016 at 12:26
1

The hard limit would be dependent on the datatype you use for the auto increment field, but once hit that threshold you'd get an overflow error of some type. If that really is a concern, then I'd suggest using something that wouldn't be as limiting such as a GUID (uuid).

http://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/ http://www.electrictoolbox.com/mysql-guid-uuid-default-column/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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