Choosing random strings using MySQL

Question

I have seen many topics on this subject, and I have not been successful in understanding how to do this.

For example, if I have this table:

+------+-------+-------+
| id   | name  | class |
+------+-------+-------+
|    5 | test  | one   | 
|   10 | test2 | one   | 
|   12 | test5 | one   | 
|    7 | test6 | two   | 
+------+-------+-------+

and I want to show random only X lines from the class "one", how can I do this?

note: its a large table, so I don't want to use ORDER BY RAND.

Answer 1

The solution ORDER BY RAND()that most people recommend is not scalable for 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 will talk about this and other solutions in my book, SQL Antipatterns: Avoid Database Programming Errors .

---

PHP, - ( ):

<?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();

Answer 2

select ID, NAME, CLASS
from YOURTABLE
where CLASS='one'
order by rand()
limit $x

rand() , / .

Answer 3

SELECT * FROM `table` WHERE `class`="one" ORDER BY RAND() LIMIT 5