从没有主键的MySQL表中获取随机行 - 已优化

Question

So here's my mission: return a random row from a MySQL table with no primary key. A quick search returned this page and this solution:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1;

Unfortunately, the solution is not optimized, as this site makes clear. They propose the following fix, which is in PHP:

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` ");
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " );

Good enough as I'll be publishing this on a page, but in the meantime I need to try it in raw MySQL. So I punched in:

SELECT * 
FROM `table` 
LIMIT (SELECT FLOOR(RAND() * COUNT(*)) FROM `table`), 1;

... and got the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT FLOOR(RAND() * COUNT(*)) FROM `table`), 1' at line 3

But I don't understand, because the individual components work perfectly. If I enter:

SELECT FLOOR(RAND() * COUNT(*)) 
FROM `table`

... I get a random number from 0 up to the number of rows minus 1. Likewise, if I enter:

SELECT * 
FROM `table` 
LIMIT 2, 1

... I get the third row in the table (or the fourth row if I substitute 3 for 2, etc.)

What am I doing wrong?? Thanks in advance!

Answer 1

If you want a random sample, the following may be fast enough:

SELECT *
FROM `table` t cross join
     (select count(*) as cnt from table t) const
WHERE rand() <= 100 / cnt
ORDER BY rand()
LIMIT 1 ;

This does have to scan the table, but the sort is on roughly 100 rows, which should be fast enough.