I took the effort to time four different functions, turns out the RAND() indeed is slower than the PHP version you used. Selecting the min and max in one query is slightly faster, same goes for using nested query's. I've executed this test script with 100 dummy rows in the table. Here's the output:
And source:
PHP:
<?php
// Deprecated, don't care
mysql_connect("localhost", "root", "root");
mysql_select_db("temp");
// Fill database with random stuff
/*for ($i = 0; $i < 100; $i++) {
mysql_query("INSERT INTO greetings (greeting) VALUES ('Hello " . ($i + 1) . "')");
}*/
/* Functions here */
$g_fpStart = 0;
function startTimer() {
global $g_fpStart;
$g_fpStart = microtime(true);
}
function getElapsedTime() {
global $g_fpStart;
return (microtime(true) - $g_fpStart);
}
/* End of functions */
/* Test functions here */
function greeting1() {
$minGreeting = mysql_result( mysql_query("SELECT min(id) FROM `greetings`"), 0);
$maxGreeting = mysql_result( mysql_query("SELECT max(id) FROM `greetings`"), 0);
$rGreeting = rand($minGreeting, $maxGreeting);
$query = mysql_query("SELECT `greeting` FROM `greetings` WHERE `id` = '{$rGreeting}'");
return mysql_result($query, 0);
}
function greeting2() {
return mysql_result(mysql_query("SELECT `greeting` FROM `greetings` ORDER BY RAND() LIMIT 1"), 0);
}
function greeting3() {
$rgData = mysql_fetch_array(mysql_query("SELECT min(id), max(id) FROM greetings"));
return mysql_result(mysql_query("SELECT greeting FROM greetings WHERE id = " . rand($rgData[0], $rgData[1])), 0);
}
function greeting4() {
return mysql_result(mysql_query("SELECT greeting FROM greetings WHERE id = (FLOOR((SELECT min(id) FROM greetings) + RAND() * (SELECT max(id) FROM greetings)))"), 0);
}
/* End of test functions */
// Start counting after connection was made and functions declared
echo "<b>Take 1:</b><br />";
startTimer();
echo greeting1();
echo "<br />Executed in " . getElapsedTime() . "<br /><br />";
Sleep(1);
echo "<b>Take 2:</b><br />";
startTimer();
echo greeting2();
echo "<br />Executed in " . getElapsedTime() . "<br /><br />";
Sleep(1);
echo "<b>Take 3:</b><br />";
startTimer();
echo greeting3();
echo "<br />Executed in " . getElapsedTime() . "<br /><br />";
Sleep(1);
echo "<b>Take 4:</b><br />";
startTimer();
echo greeting4();
echo "<br />Executed in " . getElapsedTime() . "<br /><br />";
?>
I find it very odd that your results- even on the same computer- transferring data back and forth twice while letting PHP compute in between and waiting for the second result then returning is faster than transfering data back/forth once and letting it come back pre-computed. That's highly unintuitive- and usually won't be true. In cases where a relatively slow network is in between PHP and the MySQL database, less queries is just about always faster.
One reason MySQL's rand() function is so slow is because MySQL is assuming it must use filesort and a temporary table to do the work. You only want to select one random row, so that's not the right way to do it. It is NOT intuitive, and this is one of the features in MySQL (and other SQL DBs) that I cannot stand.
Please read this article- you should use one query to MySQL and let MySQL do the computation- which will almost always be faster than 2 queries and letting PHP try to compute things slowly:
You must be registered to see links
This is a rare case where more code and clarification can be much quicker than the computer language's default optimizations. It is also much harder to code. Actually, (in SQL languages especially), this turns out to be the answer to a whole lot of optimizations in the day of today.
The most important thing to do when optimizing is testing! If you do not test, you could fall into the trap of assuming less code is usually quicker, or many other intuitive traps.
For example, you want to select a single row. Much more code is needed than the link I gave you to achieve that with optimized speed. Btw, the same MySQL programmer wrote this code on StackOverflow:
You must be registered to see links
Code:
SELECT aco.*
FROM (
SELECT minid + FLOOR((maxid - minid) * RAND()) AS randid
FROM (
SELECT MAX(ac_id) AS maxid, MIN(ac_id) AS minid
FROM accomodation
) q
) q2
JOIN accomodation aco
ON aco.ac_id =
COALESCE
(
(
SELECT accomodation.ac_id
FROM accomodation
WHERE ac_id > randid
AND ac_status != 'draft'
AND ac_images != 'b:0;'
AND NOT EXISTS
(
SELECT NULL
FROM accomodation_category
WHERE acat_id = ac_category
AND acat_slug = 'vendeglatohely'
)
ORDER BY
ac_id
LIMIT 1
),
(
SELECT accomodation.ac_id
FROM accomodation
WHERE ac_status != 'draft'
AND ac_images != 'b:0;'
AND NOT EXISTS
(
SELECT NULL
FROM accomodation_category
WHERE acat_id = ac_category
AND acat_slug = 'vendeglatohely'
)
ORDER BY
ac_id
LIMIT 1
)
)
Obviously, you'll need to change some things for it to work for you. Thant entails vaguely understanding it- which like most of MySQL- is not immediately intuitive.
