How to store uuid as number?
mysql, uuid
Solution
If I understand correctly, you're using UUIDs in your primary column? People will say that a regular (integer) primary key will be faster , but there's another way using MySQL's dark side. In fact, MySQL is faster using binary than anything else when indexes are required.
Since UUID is 128 bits and is written as hexadecimal, it's very easy to speed up and store the UUID.
First, in your programming language remove the dashes
From `110E8400-E29B-11D4-A716-446655440000` to `110E8400E29B11D4A716446655440000`.
Now it's 32 chars (like an MD5 hash, which this also works with).
Since a single `BINARY` in MySQL is 8 bits in size, `BINARY(16)` is the size of a UUID (8*16 = 128).
You can insert using:
`INSERT INTO Table (FieldBin) VALUES (UNHEX("110E8400E29B11D4A716446655440000"))`
and query using:
`SELECT HEX(FieldBin) AS FieldBin FROM Table`
Now in your programming language, re-insert the dashes at the positions 9, 14, 19 and 24 to match your original UUID. If the positions are always different you could store that info in a second field.
Full example :
CREATE TABLE `test_table` (
`field_binary` BINARY( 16 ) NULL ,
PRIMARY KEY ( `field_binary` )
) ENGINE = INNODB ;
INSERT INTO `test_table` (
`field_binary`
)
VALUES (
UNHEX( '110E8400E29B11D4A716446655440000' )
);
SELECT HEX(field_binary) AS field_binary FROM `test_table`
If you want to use this technique with any hex string, always do `length / 2` for the field length. So for a sha512, the field would be `BINARY (64)` since a sha512 encoding is 128 characters long.
Problem
Based on the answer of question, UUID performance in MySQL, the person who answers suggest to store UUID as a number and not as a string. I'm not so sure how it can be done. Anyone could suggest me something? How my ruby code deal with that?