MySQL Forums
Forum List  »  Performance

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
Re: Select random rows when using UUID
379
June 13, 2016 01:36AM


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.