MySQL Auto Increment

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I have a site that is about to go into production  
and it has a couple of tables that it uses that  
could get quite large once in full swing, most of  
them have a column called "id" and it auto  
increments every time a new row is added. When  
setting this up, what do you recommend using at  
the column type and size. atm I have gone for a  
big-int with the size of 20. I wonder over time  
will 20 be enough... Should I just think of a big  
number and use it?

Anyone else in this situation and what would you do?

Re: MySQL Auto Increment

Quoted text here. Click to load it

BIGINT = (signed) -2^63-1 to 2^63-1 -or- (unsigned) 2^64-1,  which in either
case is more than you'll ever need.
2^32-1 = 4,294,967,295  (4 billion 10 digits)
2^39-1 = 549,755,813,887  (549 billion 12 digits)
2^63-1 = 9,223,372,036,854,775,807  (19 digits)
2^64-1 = 18,446,744,073,709,551,615  (20 digits)

Storage size for BIGINT is 8 bytes, the size you are refering to (20) is the
size that MySQL would use to display the
number in the command line output. I don't beleive it has anything to do
with PHP (could be wrong).

FREE Avatar hosting at

Re: MySQL Auto Increment

If you need to use a key larger than 18,446,744,073,709,551,615 you
will likely have other problems before you ever encounter a problem ---
I don't even know how to say that number :D

You are right to look to the future, but rather than worrying about the
key I would be inclined to start looking toward testing up towards the
limits of the application to see if it is even functional and start
working backwards from there.   Maybe work out how much storage you are
going to need for indexing .... I love MySQL, I really do but I worry
about huge huge databases in anything but Oracle, its slower on smaller
databases but when it comes to handling billions of records and
actually managing a database contraining kazlillions of records its
tools and functions reign supreme -- and frankly so does the
performance (in my experience** couch couch**)

Re: MySQL Auto Increment

Smitro wrote:

Quoted text here. Click to load it

   Define "could get quite large"?  If you have a system with, say, 100  
queries/s (not a bad load already), then:

100 * 3600 * 24 = 8'640'000 records / day

   An unsigned int (which I usually use myself for these kinds of  
fields) is 4294967295, so with that kind of load you will run out of  
id's in... about 1.4 years.  However, 4 billion rows is already quite a  
large table.  Are you really going to get anywhere close to that?


Site Timeline