4

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.

Daryl Gill
  • 5,464
  • 9
  • 36
  • 69

2 Answers2

4

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
ajreal
  • 46,720
  • 11
  • 89
  • 119
  • I was under the assumption that the length is how long the number can be? – Daryl Gill Apr 06 '13 at 21:46
  • no, you can refer here : http://dev.mysql.com/doc/refman/5.0/en/integer-types.html – ajreal Apr 06 '13 at 21:48
  • It would give it a nice touch if you could explain what the length is for. I also already knew that Int has the maxes you just wrote. (And if you wanted a bigger/smaller max, use BIGINT or TINYINT). But what is the difference between INT(11) and INT(3) ? That I cannot easily find in the docs. All I can find is that the syntax exists for INT(length), but the length isn't explained on that page. (http://dev.mysql.com/doc/refman/5.1/en/create-table.html) – nl-x Apr 06 '13 at 22:12
  • @nl-x: see here: http://stackoverflow.com/a/14664515/330315 and here: http://stackoverflow.com/a/6817537/330315 and here: http://stackoverflow.com/a/5634151/330315 and here: http://stackoverflow.com/a/4432037/330315 and here: http://stackoverflow.com/a/7048597/330315 –  Apr 06 '13 at 22:20
  • @a_horse_with_no_name funny. I just went googling and also found alexander kirks blog post. tnx. this was one of those mysteries i was just too lazy to really investigate. (stupid mysql docs) – nl-x Apr 06 '13 at 22:23
1

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.

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • This is totally understandable, so what your saying is to run a few benchmarks on the finished product and calculate expectations after the benchmarking process has taken place? – Daryl Gill Apr 06 '13 at 21:30
  • 1
    Sort of, except I would expect that you shoud know the answers to these questions (about projected usage of your system) within an order of magnitude certainly, without having to run any benchmarking tests. Also, the actual usage patterns in the first few weeks may not accurately reflect what you will experience the first year, or the first five years... You should have a good idea right now. take that estimate, and multiply by ten, (or by 100) and use that. That should be safe enough. what you don't need to do is make it big enough to last ten million years – Charles Bretana Apr 06 '13 at 22:04
  • To an extent yes, it's easy enough to have personal expectations on the site; but it's your expectations are exceeded when going public, or take a while to build up to the expectations. What i'm overall aiming for is to have a 'safe' base for the specified length. But the answer below has pointed something out that I did not know to begin with. Thankyou for your time though – Daryl Gill Apr 06 '13 at 22:09