MySQL Auto Increment

[Smitro]

Hey,

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?

[Norman Peelman]

Re: MySQL Auto Increment

"Smitro" <nospam@myh0use .c0m> wrote in message
news:430860f6$1 @quokka.wn.com. au...[color=blue]
> Hey,
>
> 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?[/color]

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,8 54,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).


Norm
---
FREE Avatar hosting at www.easyavatar.com

[James]

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**)

[Marcin Dobrucki]

Re: MySQL Auto Increment

Smitro wrote:
[color=blue]
> 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?[/color]

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?

/Marcin