Choose a variable number of random entries from MySQL

Question

I want to show a random entry from a database. I would like to show X the number of random entries if I choose. So I need to select the top X entries from a randomly selected list of identifiers

(There will be no more than 500 entries to choose from, unless the Earth grows significantly in size. There are currently 66 possibilities.)

This feature works, but how can I make it better?

/***************************************************/
/* RandomSite */
//****************/
//  Returns an array of random site IDs or NULL
/***************************************************/   
function RandomSite($intNumberofSites = 1) {
    $arrOutput = NULL;
    //open the database
    GetDatabaseConnection('dev');

    //inefficient
    //$strSQL = "SELECT id FROM site_info WHERE major <> 0 ORDER BY RAND() LIMIT ".$intNumberofSites.";";

    //Not wonderfully random
    //$strSQL = "SELECT id FROM site_info WHERE major <> 0 AND id >= (SELECT FLOOR( COUNT(*) * RAND()) FROM site_info ) ORDER BY id LIMIT ".$intNumberofSites.";";

    //Manual selection from available pool of candidates  ?? Can I do this better ??
    $strSQL = "SELECT id FROM site_info WHERE major <> 0;";

    if (is_numeric($intNumberofSites))
    {
        //excute my query
        $result = @mysql_query($strSQL);
        $i=-1;

        //create an array I can work with  ?? Can I do this better ??
        while ($row = mysql_fetch_array($result, MYSQL_NUM))
        {
            $arrResult[$i++] = $row[0];
        }

        //mix them up
        shuffle($arrResult);

        //take the first X number of results  ?? Can I do this better ??
        for ($i=0;$i<$intNumberofSites;$i++)
        {
            $arrOutput[$i] = $arrResult[$i];
        }
    }   

    return $arrOutput;
    }

UPDATE QUESTION: I know about ORDER BY RAND (), I just do not want to use it, because there are rumors that it is not the best for scaling and performance. I overly criticize my code. I have work, ORDER BY RAND () works, but can I do better?

. , , , , , , .

!

Answer 1

Rand orderby ? .. ...

- ( , )

Select *
from site_info
Order by Rand()
LIMIT N

N - ...

? , .

Answer 2

rand().

array_rand :

$randKeys = array_rand($arrResult, $intNumberofSites);
$arrOutput = array_intersect_key(array_flip($randKeys), $arrResult);

edit: = >

Answer 3

, , ORDER BY RAND() 66 , .

/ ( )?

, , max id , PHP, N 1 id, . , , , . .

, "id-type", , , . , 66 , , 1-66. , , , . , , PHP, " ?".

Answer 4

,

/***************************************************/
/* RandomSite1 */
//****************/
//  Returns an array of random rec site IDs or NULL
/***************************************************/   
function RandomSite1($intNumberofSites = 1) {
    $arrOutput = NULL;
    GetDatabaseConnection('dev');
    $strSQL = "SELECT id FROM site_info WHERE major <> 0;";
    if (is_numeric($intNumberofSites))
    {
        $result = @mysql_query($strSQL);
        $i=-1;
        while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
            $arrResult[$i++] = $row[0]; }
        //mix them up
        shuffle($arrResult);
        for ($i=0;$i<$intNumberofSites;$i++) {
            $arrOutput[$i] = $arrResult[$i]; }
    }   
    return $arrOutput;
    }

JPunyon

/***************************************************/
/* RandomSite2 */
//****************/
//  Returns an array of random rec site IDs or NULL
/***************************************************/   
function RandomSite2($intNumberofSites = 1) {
    $arrOutput = NULL;
    GetDatabaseConnection('dev');
    $strSQL = "SELECT id FROM site_info WHERE major<>0 ORDER BY RAND() LIMIT ".$intNumberofSites.";";
    if (is_numeric($intNumberofSites))
    {
        $result = @mysql_query($strSQL);
        $i=0;
        while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
            $arrOutput[$i++] = $row[0]; }
    }   
    return $arrOutput;
    }

OIS , .

/***************************************************/
/* RandomSite3 */
//****************/
//  Returns an array of random rec site IDs or NULL
/***************************************************/   
function RandomSite3($intNumberofSites = 1) {
    $arrOutput = NULL;
    GetDatabaseConnection('dev');
    $strSQL = "SELECT id FROM site_info WHERE major<>0;";
    if (is_numeric($intNumberofSites))
    {
        $result = @mysql_query($strSQL);
        $i=-1;
        while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
            $arrResult[$i++] = $row[0]; }
        $randKeys = array_rand($arrResult, $intNumberofSites);
        $arrOutput = array_intersect_key($randKeys, $arrResult);
    }   
    return $arrOutput;
    }

10 000 , 2 . . 3 , .

. 2- , array_rand , . .

• 1 : 12.38003755
• 2 : 12.47702177
• 3 : 12.7124153

Answer 5

mysql_query("SELECT id FROM site_info WHERE major <> 0 ORDER BY RAND() LIMIT $intNumberofSites")

, JPunyon :)

Answer 6

:

SELECT
  @nv := @min + (RAND() * (@max - @min)) / @lc,
  (
  SELECT
    id
  FROM  site_info
  FORCE INDEX (primary)
  WHERE id > @nv
  ORDER BY
    id
  LIMIT 1
  ),
  @max,
  @min := @nv,
  @lc := @lc - 1
FROM
  (
  SELECT @min := MIN(id)
  FROM site_info
  ) rmin,
  (
  SELECT @max := MAX(id)
  FROM site_info
  ) rmax,
  (
  SELECT @lc := 5
  ) l,
  site_info
LIMIT 5

.

, , , , id.

, .

Answer 7

rand() (, MySQL)...

SELECT id, rand() as rand_idx FROM site_info WHERE major <> 0 ORDER BY rand_idx LIMIT x;

Answer 8

JPunyon. ORDER BY RAND() LIMIT $N. , $arrResult, () , MySQL RAND().

function getSites ( $numSites = 5 ) {

    // Sanitize $numSites if necessary

    $result = mysql_query("SELECT id FROM site_info WHERE major <> 0 "
                         ."ORDER BY RAND() LIMIT $numSites");

    $arrResult = array();

    while ( $row = mysql_fetch_array($result,MYSQL_NUM) ) {
        $arrResult[] = $row;
    }

    return $arrResult;
}