如何回应数据库中的随机行？

Question

I have a database table with about 160 million rows in it.

The table has two columns: id and listing.

I simply need to used PHP to display 1000 random rows from the listing column and put them into <span> tags. Like this:

<span>Row 1</span>
<span>Row 2</span>
<span>Row 3</span>

I've been trying to do it with ORDER BY RAND() but that takes so long to load on such a large database and I haven't been able to find any other solutions.

I'm hoping that there is a fast/easy way to do this. I can't imagine that it'd be impossible to simply echo 1000 random rows... Thanks!

Answer 1

ORDER BY RAND() is a mysql function working fine with small databases, but if you run anything larger then 10k rows, you should build functions inside your program instead of using mysql premade functions or organise your data in special manners.

My suggestion: keep your mysql data indexed by auto increment id, or add other incremental and unique row.

Then build a select function:

<?php
//get total number of rows
$result = mysql_query('SELECT `id` FROM `table_name`', $link); 
$num_rows = mysql_num_rows($result); 

$randomlySelected = [];

for( $a = 0; $a < 1000; $a ++ ){

        $randomlySelected[$a] = rand(1,$num_rows);

}

//then select data by random ids
$where = "";

$control = 0;
foreach($randomlySelected as $key => $selectedID){

    if($control == 0){

        $where .= "`id` = '". $selectedID ."' ";

    } else {

        $where .= "OR `id` = '". $selectedID ."'";

    }
    $control ++;
}


$final_query = "SELECT * FROM `table_name` WHERE ". $where .";";
$final_results = mysql_query($final_query);    

?>

If some of your incremental IDs out of that 160 million database are missing, then you can easily add a function to add another random IDs (a while loop probably) if an array of randomly selected ids consists of less then required.

Let me know if you need some further help.