Consider INT UNSIGNED - still 4 bytes, but twice the range.
UUIDs (and MD5s) are horrible keys because they are random, at least if
the table cannot be cached in RAM. If it fits in RAM, no problem.
The best for UUIDs: BINARY(36) (if hex with dashes) or BINARY(16) (if
packed). This avoids the 3x bloat if you are using utf8 otherwise. VAR
is unnecessary because they are fixed length.
On 10/27/11 8:17 AM, Marcus Bointon wrote:
> 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
Rick James - MySQL Geek