MySQL Forums
Forum List  »  General

Re: random sorting - what is the quickest method?
Posted by: Rick James
Date: February 12, 2011 12:34PM

SELECT * FROM tbl ORDER BY rand() LIMIT 1;
will collect all the columns, plus the extra 'column' rand(), put all such rows into a temp table, sort by the rand col, then deliver 1 row. That is, it shovels around a lot of data.

Assuming you have a UNIQUE key `id` in the table, this will make the above statement faster:
    SELECT * FROM tbl
        WHERE id = (
            SELECT id FROM tbl
            ORDER BY rand() LIMIT 1
                   );
First, the subquery builds a temp 2-column (id & rand) temp table, sorts it, and delivers the first id. Then you use that one id to fetch one row. This is (usually) faster because the temp table is much less bulky.

Note: I often exhort users to "turn that subquery into a JOIN!". This is one of the exceptions. It is an exception because it delivers fewer rows (only 1) than it scans (the whole table).

A minor bug in the prepped table, fixed:

Quote

If fetching a random row is a common query, then here's one way to prep the table:
1. add a column that contains rand(). (Let's call it `rnd`.)
2. ADD INDEX(rnd)
3. SELECT ... WHERE rnd > rand() LIMIT 1;
If rand() is high enough, step 3 will return nothing. Your code needs to deal with such, perhaps by simply trying again.

Options: ReplyQuote


Subject
Written By
Posted
Re: random sorting - what is the quickest method?
February 12, 2011 12:34PM


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.