随机记录Mysql PHP [英] Random Records Mysql PHP

问题描述

我读到，在大型数据库上使用ORDER BY RAND()运行SQL查询不是一个好主意.

I've read that it isn't a good idea to run SQL queries with ORDER BY RAND() on large databases.

所以这是我分解代码的决心.该代码需要从数据库中选择10个随机ID，然后再进行第二次选择以获取随机行.

So here's my shot at breaking up the code. The code needs to select 10 random ids from the database, then do a second select to grab the random rows.

$sql = "SELECT id FROM table WHERE image != ''
        ORDER BY id DESC LIMIT 50;";

$result = mysql_query($sql);


while($row = mysql_fetch_array($result))
{
    foreach($row as $key => $value)
    {
        $array[] = $value;
    }
}
$rand_keys = array_rand($array, 10);

foreach($rand_keys as $value)
{

    $rand_arr[] = $array[$value];

}
$rand_list = implode("," , $rand_arr);

$sql = "SELECT image FROM table
        WHERE image != ''
        AND id IN ($rand_list)";
$result = mysql_query($sql);

有什么建议可以加快或简化吗?

Any suggestions to speed up or simplify?

推荐答案

五四件事:

1. 如果只需要12个id，为什么要提取50个id?这个词的一般含义-是您有意从中选择随机行的行的子集吗?).

1. Why are you fetching 50 ids if you only need 12? (You're picking 12 ids from the last 50 - that makes sense, though is not particularly random in the general sense of the word - is that intentionally the subset of your rows you want to pick random rows from?).

您是否已对SQL语句ORDER BY RAND()进行了分析，以查看它对您来说是否很慢?您的数据集有多大?

Have you profiled the SQL statement ORDER BY RAND() to see if it's slow for you? How large is your dataset?

在上一个查询中不需要WHERE image != ''，因为您只选择了image != ''的id.

You don't need the WHERE image != '' in the last query, since you've already only picked out ids for which image != ''.

为什么要执行array_rand($array, 10)-您说要12个值?

Why are you doing array_rand($array, 10) - you say you want 12 values?

您可以像这样简化对随机值的选择:

You can simplify the picking out of random values like this:

$rand_arr = array_rand(array_flip($array), 12);

这篇关于随机记录Mysql PHP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！