Welcome!

Join our community of MMO enthusiasts and game developers! By registering, you'll gain access to discussions on the latest developments in MMO server files and collaborate with like-minded individuals. Join us today and unlock the potential of MMO server development!

Join Today!

PHP Function [Get Greeting]

Skilled Illusionist
Joined
Jun 21, 2010
Messages
324
Reaction score
54
Just wondering about this function that I just made and wondering if it could improve it, or point out any errors that I encountered. I just want to make sure I have made my code efficient and written properly.

Here's the function:
PHP:
	public function Greeting() {
		$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);
	}

and I'm just echoing the function so I can use it. Any feedback?
 
Praise the Sun!
Member
Joined
Dec 4, 2007
Messages
2,502
Reaction score
986
Just wondering about this function that I just made and wondering if it could improve it, or point out any errors that I encountered. I just want to make sure I have made my code efficient and written properly.

Here's the function:
PHP:
    public function Greeting() {
        $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);
    }

and I'm just echoing the function so I can use it. Any feedback?

There's several ways to optimize this, I think this would be your best bet (untested):

PHP:
public function Greeting() {
    return mysql_result(mysql_query("SELECT `greeting` FROM `greetings` ORDER BY RAND() LIMIT 1"), 0);
}

Keep in mind this returns a boolean (and throws a PHP error) rather than a string if no rows exists in that table.
 
Skilled Illusionist
Joined
Jun 21, 2010
Messages
324
Reaction score
54
Doesn't that slow down MySQL due to it has to create temporary tables to resort and then return. I just happened to read that on some forum a little while back. I know it's nothing serious, but I thought it might of been better to have PHP run everything, rather the database do the work.

There's several ways to optimize this, I think this would be your best bet (untested):
Keep in mind this returns a boolean (and throws a PHP error) rather than a string if no rows exists in that table.

That can be fixed with a bit of if and else though.
 
Praise the Sun!
Member
Joined
Dec 4, 2007
Messages
2,502
Reaction score
986
Doesn't that slow down MySQL due to it has to create temporary tables to resort and then return. I just happened to read that on some forum a little while back. I know it's nothing serious, but I thought it might of been better to have PHP run everything, rather the database do the work.

MySQL creates temporary tables for lots of other things, so that's not really an issue. It's better performance to have the database do the work considering it's native and PHP code gets compiled every runtime. It's usually not much of a performance gain, but still.

You might also use nested query's to do this. Might be useful to check all 3 options and time them to find out what option is the fastest to perform.
 
Praise the Sun!
Member
Joined
Dec 4, 2007
Messages
2,502
Reaction score
986
Cheers for that mate, appreciate your reply. :)

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:

Take 1:
Hello 88
Executed in 0.000494003295898

Take 2:
Hello 22
Executed in 0.00251221656799

Take 3:
Hello 67
Executed in 0.000434875488281

Take 4:
Hello 70
Executed in 0.000453233718872

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 />";
?>
 
Joined
Jun 8, 2007
Messages
1,985
Reaction score
490
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:



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:

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.
 
Last edited:
Praise the Sun!
Member
Joined
Dec 4, 2007
Messages
2,502
Reaction score
986
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.

I'm aware of that and was rather surprised myself, but apparently it's true. I've ran it again for the heck of it.

Take 1:
Hello 9
Executed in 0.000427961349487

Take 2:
Hello 38
Executed in 0.00257802009583

Take 3:
Hello 51
Executed in 0.000396966934204

Take 4:
Hello 76
Executed in 0.000440120697021

For some reason, the third function is faster than having MySQL do all the work. Even the first function that uses three query's is faster.
 
Joined
Jun 8, 2007
Messages
1,985
Reaction score
490
I'm aware of that and was rather surprised myself, but apparently it's true. I've ran it again for the heck of it.



For some reason, the third function is faster than having MySQL do all the work. Even the first function that uses three query's is faster.
over 5x faster at that.
 
Ginger by design.
Member
Joined
Feb 15, 2007
Messages
2,340
Reaction score
653
You can do a compound query that gets min/max and returns the limit/offset to pick out a random value. It should be faster than anything you can do in PHP. Sorting the entire index by RAND() will be slow.
 
Back
Top