Why Specify a Length for an Auto Increment ID

Question

I have been working with SQL for about 2 years now, and it has always been on my mind.

Best practices say assign the length of the column to what you are expecting

The SQL wants a specific row dedicated as a Primary key but it's also in best practice for an A_i field... But what length to assign it? If left blank it defaults to 11, which represents 999,999,999

Which seems fine, but best practices also state never to actually clear anything from a database; just append a 0 or 1 to represent deleted, this is for archival/recovery purposes.. Also can be used for auditing of what users want to clear..

Take this example:

I have a website which is around for years, following the best practices in terms of not deleting anything from the database; my database/website traffic is very heavy with tons of unique users/visitors per day.

Now, if I leave the SQL default length of 11, what would happen if my table reaches the maximum length and then another user decides to register? It would throw an error and not continue, which will bring up a small amount of downtime for new users reason being is that a Database administrator will have to login to the SQL and change the length.. Which is not much effort, but it IS effort which can be avoided during the early development..

---

What I do, when creating a table is give a length of 255 which in the back of my mind, something is telling me 'this is not good practice' but it avoids the very slim possibility of the example stated above.

When compared to a text field, which does not have a specified length, why cannot this be the same in terms of an A_I field.

Don't get me wrong, I completely understand the data types available.

---

I have performed an amount of research both through google and SO, but the results have pointed to questions about altering the table to increase the current length. This is not what i'm asking for.

---

Overall:

So overall, what I am trying to ask; what is the ideal length for an A_I field? to minimize the slim risk of an error being thrown if it maxes out the length but also keeping best practices in mind.

Answer 1

The reason is simple,
being as a primary key, the ID should be just well fit for what you are expecting.
If you specify a varchar, the drawback is bigger size on index,
which could be slow down both read and write performance.

int(11) .. does not store up to 99,999,999,999.
It only store up to 2,147,483,647.

If you set it to unsigned,
then it can allow 4,294,967,295 of records (4 billion!)

Facebook has just over 1 billion of users!
So, I dun see anyone can has a 4 time bigger user base anytime soon...

Couple of the best practices has been explained very well in this article:

http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

1. Smaller Columns Are Faster
2. integer are fixed length, but varchar are not fixed length
3. Index and Use Same Column Types for Joins

Answer 2

Analyze your application, or system. Estimate How many users will register per day? per year? once you know this, Then decide how "safe" you want to be - in terms of how many years you want the system to run without the need to modify this. Say 100 years is enough... So, multiply the expected number of annual user registrations by 100 and make sure the PK is large enough to accompodate that many values.