MySQL Forums
Forum List  »  Performance

Re: Select random rows when using UUID
Posted by: Rick James
Date: June 18, 2016 01:35AM

RIGHT( HEX( (1<<24) * (1+RAND()) ), 6)

That finds a random starting point within the table, assuming that UUID is indexed.

1<<24 is 2**24, which is 1048576.
1+RAND() is a number between 1 and 2.
The product of those is a number between 1048576 and 2097152.
The HEX of that would be a 7-digit hex number.
RIGHT(,6) gets the bottom 6 -- random and a full 6 hex digits, possibly with leading zeros.

This would have been simpler, but then there would be trouble with not getting leading zeros:
HEX( (1<<24) * RAND()

WHERE HEX(LEFT(y.id, 6)) > init.start
   OR HEX(LEFT(y.id, 6)) < init.start -- why do you have this?

One rule in optimization is to not hide an indexed column (y.id) inside a function (HEX(LEFT(...))). So, this might be better:
WHERE y.id > HEX(init.start)

But that again gets in trouble with leading zeros.

Here's another way to deal with such:
RIGHT(CONCAT('000000', init.start), 6)
Better yet would be to fold that into init.

But...
FLOOR(RAND() * (@max - @min) + @min)
is useful only for consecutive value, such as with AUTO_INCREMENT, not with UUIDs.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Select random rows when using UUID
1095
June 18, 2016 01:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.