How can I speed up the MySQL UUID v4 stored function?

Question

I am trying to write a MySQL stored function to generate vU UIDs as described in RFC 4122, section 4.4 ( http://www.ietf.org/rfc/rfc4122.txt ). My initial naive effort after a few tricks is as follows:

CREATE FUNCTION UUID_V4() RETURNS BINARY(16) READS SQL DATA BEGIN SET @uuid = CONCAT( LPAD( HEX( FLOOR( RAND() * 4294967296 ) ), 8, '0' ), LPAD( HEX( FLOOR( RAND() * 4294967296 ) ), 8, '0' ), LPAD( HEX( FLOOR( RAND() * 4294967296 ) ), 8, '0' ), LPAD( HEX( FLOOR( RAND() * 4294967296 ) ), 8, '0' ) ); SET @uuid = CONCAT( SUBSTR( @uuid FROM 1 FOR 12 ), '4', SUBSTR( @uuid FROM 14 FOR 3 ), SUBSTR( 'ab89' FROM FLOOR( 1 + RAND() * 4 ) FOR 1 ), SUBSTR( @uuid FROM 18 ) ); RETURN UNHEX(@uuid); END 

The above function is rather slow: almost 100 times slower than the built-in UUID() , according to the MySQL BENCHMARK() function. With the exception of writing UDF using the MySQL C API, are there any improvements I can make here, say, save an order of magnitude from its runtime?

If there is an existing, well-priced UDF or UDF stored procedure, I would also be happy to know about it.

Answer 1

I have not tested this for correctness or performance. It is just an idea to make only one concatenation instead of two.

 create function uuid_v4() returns binary(16) begin set @h1 = lpad(hex(floor(rand() * 4294967296)), 8, '0'); set @h2 = lpad(hex(floor(rand() * 4294967296)), 8, '0'); set @h3 = lpad(hex(floor(rand() * 4294967296)), 8, '0'); set @h4 = lpad(hex(floor(rand() * 4294967296)), 8, '0'); set @uuid = concat( @h1, substr(@h2 from 1 for 4), '4', substr(@h2 from 6), substr('ab89' from floor(1 + rand() * 4) for 1 ), substr(@h3 from 2), @h4 ); return unhex(@uuid); end ; 

Also why are you using READS SQL DATA in your function?