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


Advanced Search

Re: Select random rows when using UUID
Posted by: Rick James ()
Date: June 13, 2016 01:36AM

Yep, I'm listening.

Do you already have a UUID in the table? Is it indexed?
* No uuid in table --> no advantage in uuid over other techniques in my blog: http://mysql.rjweb.org/doc.php/random
* uuid column exists, but it no indexed --> dubious advantage
* uuid exists and there is a secondary index on it --> This has performance issues as discussed in http://mysql.rjweb.org/doc.php/uuid . But if you already have such an index, then it could be useful
* uuid is the PRIMARY KEY --> good advantage for the task of picking some random rows.

So, if you start with a random UUID, go forward for some LIMIT, that will get that many "random" rows. Well, not perfectly random, but probably adequately random.

SELECT ... FROM ...
WHERE uuid > RIGHT( HEX( (1<<24) * (1+RAND()) ), 6)
LIMIT 20;

will easily pick 20 rows.

The flaws include:
* If that random expression lands near the 'end' of the uuids, you might get less than 20. (This will be rare; simply try again.)
* Another fetch of 20 'random' rows could overlap this 20, thereby getting a similar set. (This may not be a serious issue.)

This technique works whether the UUID is a primary or secondary key. It will be more efficient if PRIMARY, since the rows will be 'clustered' together. If UUID is not indexed, then there is not much advantage in using it.

Options: ReplyQuote


Subject Views Written By Posted
Select random rows when using UUID 633 Jonathan Keen 06/09/2016 11:43AM
Re: Select random rows when using UUID 331 Rick James 06/13/2016 01:36AM
Re: Select random rows when using UUID 370 Jonathan Keen 06/14/2016 11:23AM
Re: Select random rows when using UUID 329 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.