random data using php & mysql

问题:

I have mysql database structure like below:

CREATE TABLE test (
    id int(11) NOT NULL auto_increment,
    title text NULL,
    tags text NULL,
    PRIMARY KEY (id)
);

data on field tags is stored as a comma separated text like html,php,mysql,website,html etc... now I need create an array that contains around 50 randomly selected tags from random records.

currently I am using rand() to select 15 random mysql data from database and then holding all the tags from 15 records in an array. Then I am using array_rand() for randomizing the array and selecting only 50 random tags.

$query=mysql_query("select * from test order by id asc, RAND() limit 15");
$tags="";
while ($eachData=mysql_fetch_array($query)) {
    $additionalTags=$eachData['tags'];
    if ($tags=="") {
        $tags.=$additionalTags;
    } else {
        $tags.=$tags.",".$additionalTags;
    }
}

$tags=explode(",", $tags);
$newTags=array();
foreach ($tags as $tag) {
    $tag=trim($tag);
    if ($tag!="") {
        if (!in_array($tag, $newTags)) {
            $newTags[]=$tag;
        }
    }
}

$random_newTags=array_rand($newTags, 50);

Now I have huge records on the database, and because of that; rand() is performing very slow and sometimes it doesn't work. So can anyone let me know how to handle this situation correctly so that my page will work normally.

回答1:

Never ORDER BY RAND() - it's horrible for performance. Instead do the randomizing in PHP. Something like this, since your ID is auto incrementing (may not be the best approach):

$count = mysql_fetch_assoc(mysql_query("select count(1) as count from test"));
$range = range(0, $count['count']);

$selection = array_rand($range, 50);
$sel_list = implode(',', $selection);

$query = mysql_query("select * from test where id in ($sel_list)");

By the way, why are you putting your tags in a string list, only to explode that string later? Just put them into an array from the start.

回答2:

I think that you're probably aware of why ORDER BY RAND() is slow. The query reads all the records, then orders them without the aid of an index.

If you select a random number between 0 and MAX(id) - 15 and get the next 15 rows, will it be sufficiently random? Are the records entered in an unordered way?

SELECT * FROM test
WHERE id >= my_random_value
ORDER BY id
LIMIT 15

回答3:

Marcus procedure can be improved, for example

SELECT * FROM test
WHERE id % round(rand()*(SELECT count(*) FROM test)) = 0
ORDER BY id
LIMIT 15

(and it is not too slow). The only problem is that the above expression does not guarantee that you will get 15 records. Would you like something like this? It could be improved to guarantee 15 records.