Random row from table

Question

MySQL

How to output a random row from a MySQL database table.

Answer 1

If the plate is not very large,
Select * From TABLE Order By rand() Limit 1;

Answer 2

I strongly advise you to google on this topic. but in honor of the holiday I will give a ready answer, but promise to google, the information is very useful. I tested it on a million real records, smartly.
SELECT pk, data
FROM test AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(pk)
FROM test)) AS id)
AS r2
WHERE r1.pk >= r2.id
ORDER BY r1.pk ASC
LIMIT 1;


Answer 3

The fastest way to generate keys by which to select in php or another language and select in the database through WHERE is the fastest way. But there should be no holes in the table, so form a temporary table without holes and already select from it, well, re-create it after some time by cron. Here is a selection of 7 records:

<?php
$max_sort_n = $db->query("SELECT MAX(sort_n) FROM users", array(), "el");

$sort_n = rand(7, $max_sort_n);

if($max_sort_n)
{
  $res = $db->query("SELECT id, login FROM users WHERE sort_n BETWEEN ?i AND ?i", array($sort_n - 6, $sort_n), "assoc");
}
?>

Answer 4

I did this:
1. You calculate the total number of rows in the table with pom. COUNT(*). We put a number in $count.
2. On php you generate a random number, from 1 to $count. We put in $rnd.
3. You make a SELECT selection, and in the limit you specify LIMIT $rnd, 1.

Answer 5

habrahabr.ru/blogs/mysql/54176/
habrahabr.ru/blogs/mysql/55864/
habrahabr.ru/blogs/mysql/104366/
habrahabr.ru/qa/1269/

Answer 6

I wonder why the option with the COUNT subselect and OFFSET + LIMIT 1 did not occur to anyone. Don't forget to subtract 1 from COUNT before multiplying by rand().