Thank you for your fast answer.
I agree when you say it is a waste of time but besides using an uuid, is
there any other way you can use an autoinc value to a table where a lot of
rows will be added on a daily basis?
Furthermore, having this as an Int, you have till the 2147483647 id until
the limit is reached, so you can have 2147483647 rows (regarding that each
one has an increment of the pk of 1 regarding the previous inserted row).
This in space requires x4 bytes (from the manual).
What I mean is - I'm able to store rows until the pk (autoinc) reaches
2147483647, right? So, changing the increment value I will have this / 10 ->
214748364 available rows to use.
Am I reading this correctly?
Furthermore, what is your policy regarding "cleaning out" the database? I
mean lets say you have a bunch of records that you no longer need (you would
store them in a file in case you need them later).
Is deleting this files and reassigning the autoincrement value a good
policy? (delete from record 1 to record 1.000.000 and reassign the
autoincrement to 1)
Thanks a lot.
From: Marcus Bointon [mailto:marcus@stripped]
Sent: quinta-feira, 27 de Outubro de 2011 14:10
To: Replication replication
Subject: Re: Master-Master -> duplicate entry
On 27 Oct 2011, at 14:55, Ricardo wrote:
> Currently I have as a a pk, an autoincrement value as int(9)
> I should be able to reach 999.999.999 records, right?
> Placing the autoincrement as 10, I will have 10x less so I will have
99.999.999 (99 million records).
> Is this correct?
> Since I already have around 125.000.000 records, I guessing implementing
this without clearing some records, will pretty much ruin everything.
> Any advice?
> I can use an autoincrement of 5 for instance. Guessing having master 1
with offset of 1 and master 2 with an offset of 2, I feel i will not have
much of a problem here.
The number after the field isn't anything to do with its stored size or
value range, just how it's displayed. MySQL uses typical binary ranges for
its numeric types, so an unsigned integer is typically 32 bits, giving a
range of 0 to 4294967295 (about 4 billion). There are other integer types:
tinyint(8 bits) smallint(16 bits), mediumint(24 bits), int(32 bits),
bigint(64 bits), all of which may be signed or unsigned. PHP only handles
signed ints so the biggest value it can handle in 32-bits is 2147483647,
which is the limit you're most likely to hit. Using a signed integer type
for an autoinc field is just a waste of a bit as you'll never use the
negative half of the number range.
Signed integers default to int(11) because they can have up to 10 base-10
digits plus a sign character; unsigned defaults to int(10) because it
doesn't need the extra space for the sign. Both store their values in 32
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK info@hand CRM solutions
marcus@stripped | http://www.synchromedia.co.uk/
MySQL Replication Mailing List
For list archives: http://lists.mysql.com/replication