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

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.

Jonathan Phillips is a web developer from the UK, specializing in PHP & MySQL plus HTML & CSS (of course). He also dabble in Internet marketing (SEO & PPC) and design. When not writing articles for Onextrapixel and his own blog, divisionbyzero.co.uk, he works at his company, Phillips Internet Limited.

Comments

  1. / Reply

    Hi Jonathan,

    just my 2 cents : anybody who had worked on large scalable projects would never consider overloading a (not so performant) database with programming stuff.

    best, Marin

  2. / Reply

    I have one question, personally I think that PHP functions are faster than Mysql functions when this functions are in WHERE clauses, because comparisons in Mysql are faster when constants are involved. However, in most cases Mysql are easier to maintain and update than PHP.

  3. / Reply

    Basically i would say let the database do what the database can do. Every line of code you can save with an intelligent query is a good line of code. Of course there are problems you cannot solve with a query or the query becomes too slow.

    Also you should not forget the amount of memory that large resultsets consume.

    • Zafar Hussain Luni,
    • June 24, 2010
    / Reply

    When you think of scalability database performance becomes the bottleneck. Why would any one overload a database with stuff which should be done in language. I most of the web sites issue is of scalability and performance due to I/O. I get performance of 2.5 sec you might be giving up scalability.

    • Kjartan,
    • June 25, 2010
    / Reply

    I wouldn’t recomeend this for performance, but it makes the code alot nicer to use AVG, SUM, DATE_FORMAT than to do it in PHP.

    • Matt,
    • June 25, 2010
    / Reply

    Do note that using strtotime() in PHP is a complicated function. If you were to have MySQL return a unix timestamp and then format the date (since date formatting, not date parsing is what you were testing) the difference between the two might not be as stark. Also using a series of substr() and strpos() calls to parse the MySQL date string along with mktime() will most certainly be faster than strtotime().

    • jon,
    • June 28, 2010
    / Reply

    Brilliant. Read every one of the 5 articles. Thanks for your hard work, its much appreciated.

  4. / Reply

    $sql = sum total/count(*) as avgQuery from table “;
    test 1 ?
    count a bit slow in mysql innodb .use num_rows instead

    ** php array much faster in sorting

    • OIS,
    • July 26, 2010
    / Reply

    Of course its faster to send one row instead of 10k.
    But if you have to download all the rows anyway (and its not a query which will benefit from query cache) you should do conversions in PHP so you dont put more load on the db.

    • Joe,
    • August 13, 2010
    / Reply

    As a couple of the previous comments noted, this approach becomes very problematic when you have to scale a web app up to thousands of users.

    If MySQL had a reliable cluster approach, it would not be so much of an issue, but most sites probably run multiple apache machines connecting to a common database machine (or maybe a couple, if read and write are separated well in the code).

    Asking your database to perform date formatting, string formatting, and other tasks that could be handled in the fron-end is probably going to become a problem when the number of users goes into the thousands (or even hundreds in a couple of apps I’ve seen). This also can cause issues with proper index utilization if the functions are used on the wrong side of a query (I’ve seen it done – it’s ugly).

    My advice is to use PHP for as much as possible. You can always add another web server, but redesigning your database because of performance issues is no picnic.

      • Gordon,
      • November 1, 2012
      / Reply

      Does the system always have to scale up to thousands of users though? Getting PHP to do all the work means a lot more traffic between PHP and the DB, and it can mean (depending on the workload) worse performance by getting PHP to do the work instead of the DB.

      There is never one “You should always do this” solution to anything in programming. If there was, everyone would be doing it! Optimizing code in one way tends to deoptimise (pesimise?) it in others. Programming is always about compromise and choosing the best solution for a given situation rather than sticking dogmatically to one given technique.

    • Joe,
    • August 14, 2010
    / Reply

    One last thought:

    If anyone is inclined to take this approach, you should certainly hope and pray that the powers that be in your organization never decide to change to another DB on the back-end. It would not be fun to tell them that you had bound your app to the DB so tightly that you will need a full re-write to change database engines.

    • dll,
    • February 8, 2011
    / Reply

    Great article and discussion, exactly what I was looking for! Looking to format a date stored on my DB mysql “yyyy-mm-dd” to “yyyy-MMM-dd”. I’ve written both a php function to change the mm to MMM , and also made an implementation in mySQL using UPPER() and DATEFORMAT() functions.

    I was tempted to use the mySQL function to do the formatting, but I am expecting my webApp to receive thousands of users a day. I think overtime this will create a huge burden on the DB so I will most likely have my php coding do most of the work.

    Thanks again for all your input!

    • Gordon,
    • November 1, 2012
    / Reply

    Just a few points I’d like to make.

    1) The doing it all in PHP means a lot more traffic between the database and the PHP runtime (in your example, 1 row versus 10,000 rows). What would the PHP performance be like if you used a 10,000 element pre-built array instead of pulling it from a database?

    2) With MySQL at least, by default the driver always returns strings for each column value, even if there is a more suitable equivalent PHP datatype. Whenever you do mathematical operations on strings, PHP has to cast them. Would your benchmarks be significantly faster if the data started off in numeric format to begin with?

    3) There are still cases where you’d want to do some of the work in PHP instead of on the database. If, for example, you have 10 web servers each running the PHP code, all talking to the same database. If all calculation work is done on the database then the database has to do considerably more work, all being done on the same box. If the PHP scripts are doing the calculations, then while each individual instance of the script may take longer, the overall load balancing would be better as the work is distributed across the 10 web server boxes.

    There’s a lot more to performance than simple benchmarks. A solution that might be fast in one scenario may not scale in another.

Leave a Reply

Your email address will not be published. Required fields are marked *

Deals

Iconfinder Coupon Code and Review

Iconfinder offers over 1.5 million beautiful icons for creative professionals to use in websites, apps, and printed publications. Whatever your project, you’re sure to find an icon or icon…

WP Engine Coupon

Considered by many to be the best managed hosting for WordPress out there, WP Engine offers superior technology and customer support in order to keep your WordPress sites secure…

InMotion Hosting Coupon Code

InMotion Hosting has been a top rated CNET hosting company for over 14 years so you know you’ll be getting good service and won’t be risking your hosting company…

SiteGround Coupon: 60% OFF

SiteGround offers a number of hosting solutions and services for including shared hosting, cloud hosting, dedicated servers, reseller hosting, enterprise hosting, and WordPress and Joomla specific hosting.