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.