MySQL Forums :: Performance :: Select random rows when using UUID


Advanced Search

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
Select random rows when using UUID 674 Jonathan Keen 06/09/2016 11:43AM
Re: Select random rows when using UUID 353 Rick James 06/13/2016 01:36AM
Re: Select random rows when using UUID 391 Jonathan Keen 06/14/2016 11:23AM
Re: Select random rows when using UUID 348 Rick James 06/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.