Is it faster to use php array count() than SQL row count?

Question

In a nutshell: Is it faster to use PHPs array count() on a number of arrays vs. using SQL row count multiple times?

I'm having an issue with a slow query that I attribute to the COUNT(*) function. I will explain what I am doing and then what I'm anticipating might be significantly faster.

Currently I'm looping a function that does a count of about 20,000 rows each iteration. It returns the number of rows for each month in a year:

// CREATE MONTHLY LINKS
public function monthly_links() {

    $months = array('','January','February','March','April','May','June','July','August', 'September','October','November','December');
    for ($i=1 ; $i <= 12 ; $i++) {
        $array[] = "<a href='monthly.php?month=" . $i . "&status=3'>" . $months[$i] . " " . $this->chosen_year() . " (" . $this->number_positions_month_year($i, $this->chosen_year()) . ")</a>";
    }
    return $array;

}


// SHOW NUMBER OF POSITIONS FOR EACH MONTH/YEAR
public function number_positions_month_year($month, $year) {

    $sql = $this->client_positions_sql() . " AND MONTH(`exp_date`) = " . $month . " AND YEAR(`exp_date`) = " . $year;
    $res = $this->conn->query($sql);
    $count = $res->num_rows;

    return $count;
}

The code is not that important in the example above because essentially what I am asking is: Is it faster to do the following...?

• Query the table once while dumping each months corresponding ids to an array (there will be 12 arrays)
• Using PHP count() on each array to get the number of entries for each month

Answer 1

You can use SQL's group by function to group by month.

SELECT COUNT(*), MONTH(exp_date)
FROM theTableYouAreUsing
GROUP BY MONTH(exp_date)

Then in php, in the array that's returned you get the count for the month you need.

Speed wise, this is a lot quicker than a separate query for each month.

Answer 2

In short: Premature optimization is the root of all evil :) So usually in the end it doesn't really matter. However, keep in mind that depending on when and where you need the number of rows how you fetch and handle the result you don't have the full data in a single array available, so you don't have an array to call count() on. Just to use count() seems not to be a valid reason to create such an array, because it unnecessarily consumes memory.

Answer 3

I am going to have to agree with KingCrunch on this. What you really need to look at is that type of application you are having if this is low voulme of users or something like that then doing it in the database will be faster now if you have lots of traffic blah blah blah and to avoid the db getting overloaded by soemthing php can do then php will be faster when you get to something like that at scale. Something to alsways keep in mind is that if you send the result set over to php it is going to have to recive the data over the network and then count it meaning more data and network latancy, but again that is assuming this is with a remote db. But try not to over optimize.