MySQL Has Functions (Part 5) : PHP vs MySQL Performance

Welcome to the Part 5 - final part of the series on MySQL Functions. I hope you've enjoyed it(or at least found it useful so far. You might want to check out Part 1 - Date and Time Functions, Part 2 - String Functions, Part 3 - Fun With Numbers or Part 4 - Make Functions with Stored Procedures & Triggers, in case you have missed any of them. In this final part, I thought I'd take a quick look at how MySQL performs in terms of speed versus PHP.

MySQL - PHP vs MySQL Performance


If you remember, in Part 1 - Date and Time Functions I said that the idea behind this series was that you could use MySQL's in-built functions to remove some of your PHP code, thereby making it cleaner and easier to maintain. I did, however, hypothesise that MySQL's functions would be faster than the equivalent PHP, so I guess we'd better find out.

Testing Methodology

There'd be no point running performance tests without some sort of method which tries to give results that are as accurate as possible, and which tries to take into account any anomalous readings. So here's the example:

Each test ran inside its own small PHP script to be called from the browser. Because computer code runs so fast there's no way running the code just once would give decent measurements. So I decided to run each test's code 10,000 times inside a FOR loop.

Before and after each test run I registered variables called $start and $end and assigned them values from PHP's microtime() function, which returns a Unix timestamp including microseconds. Without the microseconds part the timing wouldn't be precise enough. The time taken for the 10,000 executions of the code would therefore equal $end - $start.

The structure for each test looked something like this:

$conn = mysqli_connect(...); //connect to the DB

ob_start(); //buffer the output, we don't actually care about seeing it

$start = microtime(true);

for($i = 0; $i < 10000; $i++) //loop 10K times
{
    //test code here
}

$end = microtime(true);

ob_end_clean(); //get rid of the buffered output

$time = $end - $start;

To actually run the tests, I uploaded the scripts to one of my web servers and ran them via my browser. I ran each test 10 times, at three different times of the day, to hopefully counter any anomalous readings caused by things like background processes which were sharing CPU or disk time. An average of all the run times was then taken.

Test 1 - Calculating an Average Score

In a previous part I looked at using some of MySQL's functions to retrieve data from a table of students. I used a similar test table this time and worked out the average exam score.

When MySQL Calculates The Average

The MySQL test code looked like this:

$sql = "SELECT AVG(score) as average_score FROM school";

$result = mysqli_query($conn, $sql);

$row = mysqli_fetch_assoc($result);

echo $row['average_score'];

Simple, yes? MySQL does all the work in the query, so all PHP has to do is echo out the result.

This took on average 2.14 seconds for 10,000 iterations.

In PHP, however, things were not so straightforward.

When PHP Calculates The Average

The PHP test code for working out the average score looked like this:

$sql = "SELECT score FROM school";

$result = mysqli_query($conn, $sql);
	
$total = 0;

$num = mysqli_num_rows($result);

while($row = mysqli_fetch_assoc($result)) {
	$total += $row['score'];
}

echo ($total / $num);

A slightly simpler query, but the code had to loop over every returned row and total up all the scores, then divide that number by the number of rows via mysqli_num_rows to obtain the average.

This took 5.65 seconds to run 10,000 iterations.

End Result
Using MySQL's AVG() function was 2.64 times faster than the equivalent PHP code, so it's both tidier and faster.

What About The Query Cache?

The AVG() test was run with MySQL's query cache disabled, and I was curious to see if it would have any impact on the results if it were turned on.

I assumed that the MySQL tests would finish even faster, because of caching its query would mean no AVG() calculations would be required and I wasn't wrong.

With the query cache turned on and set to 32MB (overkill probably) the MySQL test took 0.91 seconds and the PHP test took 4.27 seconds. So this time MySQL was 4.7 times faster than PHP.

Test 2 - Formatting a Date

Way back in Part 1 - Date and Time Functions, I looked at the functions MySQL provides for messing about with dates and times, in particular the DATE_FORMAT() function, which surprisingly enough, produces formatted date strings in a manner similar to PHP's date() function.

The goal of the code on this test was to take the MySQL - formatted date (YYYY-MM-DD) field of each row, and output it as a string in the format DD/MM/YYYY.

When MySQL formats the dates

The MySQL test code looked like this:

$sql = "SELECT DATE_FORMAT(rating_date, '%D/%M/%Y') AS rating_date FROM ratings";

$result = mysqli_query($conn, $sql);

while($row = mysqli_fetch_assoc($result)) {
        echo $row['rating_date'] . ' ';
}

This code took on average 8.30 seconds to run 10,000 iterations.

For this test I used the video ratings table from Part 4, populated with lots of lovely sample data using the custom stored procedure I created.

When PHP Formats The Dates

The PHP code was more complicated this time, because of the need to first turn the MySQL date into a timestamp that could be used by PHP's date() function.

$result = mysqli_query($conn, "SELECT rating_date FROM ratings");

while($row = mysqli_fetch_assoc($result)) {
	$timestamp = strtotime($row['rating_date']);
	
	$theDate = date('d/m/Y', $ timestamp);
	
	echo $theDate . ' ';
}

I found the result of this test quite surprising: the PHP code took 39.48 seconds to run 10,000 times.

End Result
So for this instance MySQL was 4.76 times faster than the PHP equivalent.

With MySQL Query Cache Enabled

Once again I decided to run the tests again with the query cache enabled and set to 32MB.

The MySQL test took 6.25 seconds and the PHP test took 35.85 seconds, meaning that MySQL was 5.74 times faster.

In Conclusion

Using MySQL's functions was faster than using PHP's code to do the same things. I was pretty sure that calling one MySQL function would be faster than looping over a result set in PHP and potentially using multiple PHP functions to achieve the same end.

One thing to bear in mind, however, is that even though MySQL was up to 5.7 times faster than PHP, this was for TEN THOUSAND runs of the same code.

Just like almost every other code performance optimisation tips you read on the web (single vs. double quotes, for vs. while loops etc.) the real world difference will be negligible unless you're serving tens of thousands of visitors in a short space of time.

Even if you are, in that situation your mind should be on things like bigger and better servers, and load balancing - not tiny code tweaks. That's not to say optimisation shouldn't be your goal at some point, just remember:

"...premature optimization is the root of all evil" - Donald Knuth

Get your code working, tidy it up, and then worry about performance. Also, I only tested a couple of MySQL functions - others could be slower for any number of reasons, but I still maintain that the main benefit of using MySQL's functions is cleaner application code which makes maintenance and future updates easier.

Opinions expressed in this article are those of the author and not necessarily those of Onextrapixel.