Calculations in mysql SELECT vs. calculation in script

Question

Let’s say I want to calculate the percentages of records in a mysql table for certain information, such as users up to 20 years old, from 21 to 30 years old, and over 30 years old, and this table has about 100,000 records.

In terms of performance, it is better to do the calculations directly in SELECT or receive the database information to calculate in PHP or ASP script, for example?

Answer 1

Using Mysql functions is faster than using PHP code to do the same things, said in this link below.

PHP vs Mysql Performance - in English

PHP vs Mysql Performance - in English (Google translator)

============== if the page of the link above is removed we have ==============

_{Feel free to correct the translation made by Google translator}

Methodology of Tests

There is no point in running performance tests without some kind of method that tries to give as accurate results as possible, and that tries to take into account any anomalous readings. So here’s the example:

Each test was run within its own PHP script to be called by the browser. As the computer code runs so fast, it is not possible to run the code just once to get decent measurements. So I decided to run the code of each test 10,000 times within a FOR loop .

Before and after each test, I recorded variables called $start and $end and assigns to them function values microtime() PHP, which returns a Unix timestamp including microseconds. Without microseconds, time would not be accurate enough. The time spent for the 10,000 executions of the code would therefore equal $end - $start .

The structure of each test:

    $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 loaded the scripts on one of my web servers and ran them through my browser. I ran each test 10 times, at three different times of the day, to contain any anomalous readings caused by things like background processes that shared CPU or disk time. An average of all running times was then taken.

---

Test 1 - Calculating an average score

In an earlier part, I looked to use some of the Mysql functions to retrieve data from a student table. I used a similar test table this time and calculated the average test score.

When Mysql calculates the average

    $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 needs to do is echo the result.

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

In PHP, however, things were not so simple.

When PHP calculates the average

The PHP test code for calculating the average score looks 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 go through all the returned rows and total all the scores, then divide that number by the number of rows via mysqli_num_rows to get the average.

This took 5.65 seconds to perform 10,000 iterations.

---

Final test result 1

So for this instance, Mysql was 4.76 times faster than the PHP equivalent.

Using Mysql’s AVG() function was 2.64 times faster than the equivalent PHP code, so it’s clearer and faster.

About the query cache?

The AVG() test was run with the Mysql query cache disabled, and I was curious to see if it would have any impact on the results if it was enabled.

I assumed that Mysql tests would end even faster, because the cache of your query would mean that no AVG() calculation would be necessary and I was not wrong.

With query cache enabled and set to 32MB (probably Overkill), 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 - format a date

In Part 1 - Date and Time Functions, I looked at the functions that Mysql provides to play with dates and times, in particular the DATE_FORMAT() function, which surprisingly produces date() formatted date date.

The purpose of the code in this test was to take the Mysql formatted date field (YYYY-MM-DD) of each row and send it as a string in DD / MM / YYYYYY format .

When Mysql formats dates

Mysql test code looks 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 execute 10,000 iterations .

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

When PHP formats dates

The PHP code was more complicated this time, because of the need to first transform the Mysql date into a timestamp that could be used by the PHP 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.

---

Final result - Test 2

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 query cache enabled and set to 32MB.

Mysql test took 6.25 seconds and PHP test took 35.85 seconds, which means Mysql was 5.74 times faster.

---

---------- Completion

---

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

One thing to keep in mind, however, is that although Mysql was up to 5.7 times faster than PHP, this was done by ten thousand times the same code.