On 27 Oct 2011, at 16:06, Ricardo Freitas wrote:
> 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?
The problem that UUIDs solves is where you're inserting into multiple databases that are
not connected, yet you can still be sure that you won't get a clash. You can do something
similar with hashes (a UUID is really a kind of carefully constructed hash), and the best
way to store those is in BINARY type fields as you can convert to and from them via hex;
storing things like MD5 hashes in char(32) fields is 50% wasteful as hex only stores 4
bits per character.
Autoincs generally work fine, and I wouldn't try too hard to find an alternative. MySQL's
implementation is less thorough than in other DB systems - take a look at postgres for a
more traditional system - but it's perfectly adequate for most applications.
> 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).
Yes, 4 bytes == 32 bits.
> 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)
It's entirely possible to do that, but doing it with MyISAM tables is prone to orphaning
your data unless you're very careful about renumbering all your foreign keys in other
tables. It's much easier if you have foreign key constraints as in InnoDB. Overall I
wouldn't worry about it unless you are about to run out of numbers, and even then it's
easier to just switch to a bigger int type and all your problems will solved for a long
time (and if you have > 200 million records, you probably have a client to pay for it
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK info@hand CRM solutions
marcus@stripped | http://www.synchromedia.co.uk/