Please, stop using ORDER BY RAND()
. Just stop. This operation has complexity of n*log2(n)
, which means that the time spent on query would grow “
entries | time units
-------------------------
10 | 1 /* if this takes 0.001s */
1'000 | 300
1'000'000 | 600'000 /* then this will need 10 minutes */
If you want to generate random results, create a stored procedure, which generates them. Something like this (code taken from this article, which you should read):
DELIMITER $$
DROP PROCEDURE IF EXISTS get_rands$$
CREATE PROCEDURE get_rands(IN cnt INT)
BEGIN
DROP TEMPORARY TABLE IF EXISTS rands;
CREATE TEMPORARY TABLE rands ( tagname VARCHAR(63) );
loop_me: LOOP
IF cnt < 1 THEN
LEAVE loop_me;
END IF;
SET cnt = cnt - 1;
INSERT INTO rands
SELECT tags.tagname
FROM tags
JOIN (SELECT (RAND()*(SELECT MAX(tags.id) FROM tags)) AS id) AS choices
WHERE tags.id >= choices.id
LIMIT 1;
END LOOP loop_me;
END$$
DELIMITER ;
And to use it, you would write:
CALL get_rands(10);
SELECT * FROM rands;
As for executing it all on PHP side, you should stop using the ancient mysql_*
API. It is more than 10 years old and no longer maintained. Community has even begun process for deprecating them. There should not be any more new code written with mysql_*
in 2012. Instead you should use PDO or MySQLi. As for how to write it (with PDO):
// creates DB connection
$connection = new PDO('mysql:host=localhost;dbname=mydb;charset=UTF-8',
'username', 'password');
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
// executes the procedure and creates select statement
$connection->exec('CALL get_rands(10)');
$statement = $connection->query('SELECT * FROM rands');
// performs query and collects all the info
if ($statement->execute())
{
$tags = $statement->fetchAll(PDO::FETCH::ASSOC);
}
Update
If the requirement is to get not only 10 random results, but actually 10 UNIQUE random results, then it would require two changes to the PROCEDURE
:
-
The temporary table should enforce the uniqueness of entries:
CREATE TEMPORARY TABLE rands ( tagname VARCHAR(63) UNIQUE);
It also might make sense to collect just IDs and not the values. Esspecially if what you are looking for are 10 unique articles, not just tags.
-
When inserting a duplicate value is found, the
cnt
counter should not decrease. This can be ensured by adding aHANDLER
(before definition ofLOOP
), which would “catch” the raised warning, and adjust the counter:DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET cnt = cnt + 1;