Hi,
I need to use UUID as a primary key.
Initially I was going to use BINARY(16)
and generate id as UNHEX(REVERSE(REPLACE(UUID(), ‘-’, ‘’)))
It would work fine, but I’ve got a non mysql tech problem which prevents me from using binary field.
What would be another best option from performance point of view to store same compact identifier? I thought of something like decimal(38,0) or probably char(22). But I’m not sure which would be faster (and how much slower they would be compared to using binary datatype
Thanks,
Alex
Why can’t you select HEX(field)? You can even use a view if necessary.
decimal(39,0) takes fewer bytes (18), that’s why it is faster.
| [B]achudinov wrote on Tue, 01 June 2010 11:19[/B] |
|
What would be another best option from performance point of view to store same compact identifier? I thought of something like decimal(38,0) or probably char(22). But I’m not sure which would be faster (and how much slower they would be compared to using binary datatype
|
I just have to ask, do you really need to try to tweak out so much performance that you have to consider those few bytes per record?
It’s just that say that you have InnoDB and that you have 4 indexes and this field is the primary key, then you have 5 places total per row that will use this field. And if this field is 4 bytes larger per field that would increase your row+index length with 20 bytes per inserted row.
At 1,000,000 rows that is still only 20MB increased table size.
The problem with bending over backwards to get the smallest field possible in the database is that you usually end up with having to perform transformations on the way in and out of the database and by that just moving the processing load.
My point with this is that unless I work with tables that I know will become very big, where these few bytes actually will matter, I don’t really consider a few bytes here and there as a problem since the speed difference for smaller tables is negligible.
Keeping the solution simple, choosing the right type for the job and remembering to put indexes on the right columns are far more important.