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

Question

I'm attempting to write a MySQL stored function to generate v4 UUIDs as described in RFC 4122's section 4.4 ( http://www.ietf.org/rfc/rfc4122.txt ). My initial naive effort after a few tweaks is the following:

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 quite slow: almost 100 times slower than the built-in UUID(), according to MySQL's BENCHMARK() feature. Short of writing a UDF using MySQL's C API, are there any improvements I can make here to, say, shave off an order of magnitude from its runtime?

If there is an already existing, well-regarded UUID UDF or stored procedure, I'd be happy to hear about that, too.

Answer 1

I didn't test this for correctness or for performance. It is just the idea of doing one only concatenation in 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 do you use READS SQL DATA in your function?