Selecting random rows with MySQL
The ORDER BY RAND()
solution that most people recommend doesn't scale to large tables, as you already know.
SET @r := (SELECT FLOOR(RAND() * (SELECT COUNT(*) FROM mytable)));
SET @sql := CONCAT('SELECT * FROM mytable LIMIT 1 OFFSET ', @r);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
I cover this and other solutions in my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
If you want to do this with PHP, you could do something like this (not tested):
<?php
$mysqli->begin_transaction();
$result = $mysqli->query("SELECT COUNT(*) FROM mytable")
$row = $result->fetch_row();
$count = $row[0];
$offset = mt_rand(0, $count);
$result = $mysqli->query("SELECT * FROM mytable LIMIT 1 OFFSET $offset");
...
$mysqli->commit();
select ID, NAME, CLASS
from YOURTABLE
where CLASS='one'
order by rand()
limit $x
ordering by rand()
is not particularly efficient, but it's about the smallest/quickest way of doing it.