7

I saw many topics about this subject and I have been unsuccessful in understanding how to do it.

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 only X random rows from class "one", how can I do that?

NOTE: it's a big table, so I don't want to use ORDER BY RAND.

informatik01
  • 16,038
  • 10
  • 74
  • 104
Daniel
  • 147
  • 1
  • 2
  • 9
  • possible duplicate of [quick selection of a random row from a large table in mysql](http://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql) – outis Mar 30 '12 at 03:44

3 Answers3

21

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 Volume 1: 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();
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Wow, this is high level of SQL. This is possible on PHP? and its the full code which I need to write on mysqli_query()? – Daniel Jul 01 '11 at 00:17
  • Maybe it'd be usefull for that script to use [`mysqli::prepare`](http://php.net/manual/mysqli.prepare.php) – Lumbendil Jul 04 '11 at 07:29
  • 2
    @Lumbendil: I do support using parameters where appropriate. In the above example, the risk of SQL injection is minimal. We know the value came from `rand()`, and that function only returns integers. – Bill Karwin Jul 04 '11 at 14:17
  • 1
    But, in case of wanting to retrieve multiple rows, the query could be executed multiple times without having to be analyzed on multiple ocasions. – Lumbendil Jul 06 '11 at 09:50
  • For large tables will this be helpful, if I want to select one random row using order by rand(): select * from (select from large table where some condition) new_table order by rand() limit 0, 1; – thekosmix Oct 09 '13 at 06:37
  • 1
    @thekosmix, you shouldn't need to do that subquery, because regardless, the WHERE condition will limit the number of rows before it applies ORDER BY. – Bill Karwin Oct 09 '13 at 06:46
  • @BillKarwin rand() in PHP fails terribly with large datasets. I'd suggest using mt_rand() instead as the PHP docs claim it to be 4 times faster. I've found it to generate far more random results than rand(). – BizLab Feb 20 '14 at 15:04
  • With the PHP example, if it is possible that the count could shrink you should make sure to wrap the code in a SQL transaction. If not you could end up selecting an offset that is larger than the returned set. – DrewB Jul 10 '17 at 16:09
  • Nice! > impressive how "complex" it is using mysql, compared with sqlite3 method which gives you a nice integer to append on a query just right after a `select count(*) from table` – Evhz Mar 19 '19 at 13:12
2
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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
0
SELECT * FROM `table` WHERE `class`="one" ORDER BY RAND() LIMIT 5
Marco
  • 960
  • 2
  • 7
  • 26
  • Notice: I use big table, and ORDER BY RAND() bom all rows in the table and its very slowly. – Daniel Jun 30 '11 at 22:23